Click here to Skip to main content
15,887,936 members
Articles / Database Development / SQL Server
Alternative
Tip/Trick

nth Highest Salary in SQL Server 2005

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
18 Apr 2011CPOL 10K   1   1
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)


Written By
Software Developer
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
SuggestionYou can simplify like this Pin
Bala Ballzz17-Jul-14 0:21
Bala Ballzz17-Jul-14 0:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.