Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

nth Highest Salary in SQL Server 2005

0.00/5 (No votes)
18 Apr 2011CPOL 10.2K  
The original solution has some issues. Check the following script:IF OBJECT_ID('tempdb..#salary') IS NOT NULL BEGIN DROP TABLE #salary ENDCREATE TABLE #salary ( Salary INT )INSERT INTO #salary(Salary) VALUES(5) -- 1INSERT INTO #salary(Salary)...
The original solution has some issues. Check the following script:
SQL
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(DISTINCT Salary) FROM #salary b

Another solution to get the N-th highest/lowest salary is given below:

SQL
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)