nth Highest Salary in SQL Server 2005

IF OBJECT_ID('tempdb..#salary') IS NOT NULL
    BEGIN
    DROP TABLE #salary
    END
 
CREATE TABLE #salary
    (
    Salary      INT
    )
 
INSERT INTO #salary(Salary) VALUES(5) -- 1
INSERT INTO #salary(Salary) VALUES(4) -- 2
INSERT INTO #salary(Salary) VALUES(3) -- 3
INSERT INTO #salary(Salary) VALUES(3) -- 3
INSERT INTO #salary(Salary) VALUES(2) -- 4
INSERT INTO #salary(Salary) VALUES(1) -- 5

-- SALARY RANK
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 -- RESULT SHOULD BE 2
-- WRONG
SELECT salary FROM #salary a 
    WHERE (@salaryRank - 1) = (SELECT COUNT(salary) FROM #salary b WHERE b.salary > a.salary)
/* RESULT
salary
-----------
*/
-- CORRECT
SELECT  TOP 1
        Salary
FROM    #salary a
WHERE   (@salaryRank - 1) = (SELECT COUNT(DISTINCT Salary) FROM #salary b WHERE b.Salary > a.Salary)
/* RESULT
Salary
-----------
2
*/
 
SET     @salaryRank = 5 -- RESULT SHOULD BE 1
-- WRONG
SELECT salary FROM #salary a
    WHERE (@salaryRank - 1) = (SELECT COUNT(salary) FROM #salary b WHERE b.salary > a.salary)
/* RESULT
salary
-----------
2
*/
-- CORRECT
SELECT  TOP 1
        Salary
FROM    #salary a
WHERE   (@salaryRank - 1) = (SELECT COUNT(DISTINCT Salary) FROM #salary b WHERE b.Salary > a.Salary)
/* RESULT
Salary
-----------
1
*/

Please note that SELECT COUNT(salary) FROM #salary b ... is replaced with SELECT COUNT(DISTINCTSalary) FROM #salary b
 
Another solution to get the N-th highest/lowest salary is given below:
 

 Collapse | Copy Code

SET     @salaryRank = 5 -- RESULT SHOULD BE 1
-- N-th highest salary
SELECT  TOP 1
        Salary
FROM    (
        SELECT  SalaryRank  = DENSE_RANK() OVER(ORDER BY Salary DESC),
                Salary
        FROM    #salary
        ) a
WHERE   SalaryRank = @salaryRank
-- N-th lowest salary -- RESULT SHOULD BE 5
SELECT  TOP 1
        Salary
FROM    (
        SELECT  SalaryRank  = DENSE_RANK() OVER(ORDER BY Salary),
                Salary
        FROM    #salary
        ) a
WHERE   SalaryRank = @salaryRank