The original solution has some issues. Check the following script:
IF OBJECT_ID('tempdb..#salary') IS NOT NULL
BEGIN
DROP TABLE #salary
END
CREATE TABLE #salary
(
Salary INT
)
INSERT INTO #salary(Salary) VALUES(5)
INSERT INTO #salary(Salary) VALUES(4)
INSERT INTO #salary(Salary) VALUES(3)
INSERT INTO #salary(Salary) VALUES(3)
INSERT INTO #salary(Salary) VALUES(2)
INSERT INTO #salary(Salary) VALUES(1)
SELECT SalaryRank = (SELECT COUNT(DISTINCT Salary) FROM #salary b WHERE b.Salary > a.Salary)+1 ,
Salary
FROM #salary a
ORDER
BY 1
DECLARE @salaryRank INT
SET @salaryRank = 4
SET @salaryRank = 4
SELECT salary FROM #salary a
WHERE (@salaryRank - 1) = (SELECT COUNT(salary) FROM #salary b WHERE b.salary > a.salary)
SELECT TOP 1
Salary
FROM #salary a
WHERE (@salaryRank - 1) = (SELECT COUNT(DISTINCT Salary) FROM #salary b WHERE b.Salary > a.Salary)
SET @salaryRank = 5
SELECT salary FROM #salary a
WHERE (@salaryRank - 1) = (SELECT COUNT(salary) FROM #salary b WHERE b.salary > a.salary)
SELECT TOP 1
Salary
FROM #salary a
WHERE (@salaryRank - 1) = (SELECT COUNT(DISTINCT Salary) FROM #salary b WHERE b.Salary > a.Salary)
Please note that
SELECT COUNT(salary) FROM #salary b ...
is replaced with
SELECT COUNT(DISTINCT Salary) FROM #salary b
Another solution to get the N-th highest/lowest salary is given below:
SET @salaryRank = 5
SELECT TOP 1
Salary
FROM (
SELECT SalaryRank = DENSE_RANK() OVER(ORDER BY Salary DESC),
Salary
FROM #salary
) a
WHERE SalaryRank = @salaryRank
SELECT TOP 1
Salary
FROM (
SELECT SalaryRank = DENSE_RANK() OVER(ORDER BY Salary),
Salary
FROM #salary
) a
WHERE SalaryRank = @salaryRank