Click here to Skip to main content
Click here to Skip to main content
Alternative Tip/Trick

Tagged as

nth Highest Salary in SQL Server 2005

, 18 Apr 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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:
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:
 
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)

Share

About the Author

Costica U
Software Developer
United States United States
No Biography provided

Comments and Discussions

 
SuggestionYou can simplify like this PinmemberBala_MB17-Jul-14 1:21 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141216.1 | Last Updated 18 Apr 2011
Article Copyright 2011 by Costica U
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid