What is the 3rd highest salary?
- It's very simple to find what is the lowest, the highest, the top 3,... It's either a min, max, or a top...
- Finding the 3rd highest salary by itself or the range from the 3rd highest to the 10th highest. That's where people start to use cursors and temporary tables to count all the rows returned.
DENSE_RANK()
For detailed examples and explanation of **DENSE_RANK()** see: How to rank rows by grouping without using cursors with SQL Server
- The DENSE_RANK() function does not have any gap, the numbers are always consecutive.
- If 2 rows have the same value of the OVER, they will have the same DENSE_RANK(), the following row will have DENSE_RANK() + 1 and NOT DENSE_RANK() + Number of duplicates.
Data used
USE sql911;
go
SELECT empno AS 'Emp#',
firstnme AS 'First Name',
lastname AS 'Last Name',
isnull(workdept,' **') AS 'Dept',
isnull(CAST(salary AS VARCHAR(11)),' **') AS 'Salary',
isnull(CAST(bonus AS VARCHAR(11)),' **') AS 'Bonus',
isnull(CAST(comm AS VARCHAR(11)),' **') AS 'Commission'
FROM emps
ORDER BY lastname, firstnme;
go
go
SELECT empno AS 'Emp#',
firstnme AS 'First Name',
lastname AS 'Last Name',
isnull(workdept,' **') AS 'Dept',
isnull(CAST(salary AS VARCHAR(11)),' **') AS 'Salary',
isnull(CAST(bonus AS VARCHAR(11)),' **') AS 'Bonus',
isnull(CAST(comm AS VARCHAR(11)),' **') AS 'Commission'
FROM emps
ORDER BY lastname, firstnme;
go
Emp# First Name Last Name Dept Salary Bonus Commission ------ ------------ --------------- ---- ----------- ----------- ----------- 000150 BRUCE ADAMSON ** ** ** ** 000200 DAVID BROWN D11 27740.00 600.00 2217.00 000050 JOHN GEYER E01 40175.00 800.00 3214.00 000340 JASON GOUNOT E21 23840.00 500.00 1907.00 000010 CHRISTINE HAAS A00 52750.00 1000.00 4220.00 000090 EILEEN# HENDERSON E11 29750.00 600.00 2380.00 000230 JAMES JEFFERSON D21 22180.00 400.00 1774.00 000260 SYBIL\ JOHNSON D21 17250.00 300.00 1380.00 000210 WILLIAM* JONES D11 18270.00 400.00 1462.00 000030 SALLY! KWAN C01 38250.00 800.00 3060.00 000330 WING LEE E21 25370.00 500.00 2030.00 000110 VINCENZO. LUCCHESSI A00 46500.00 900.00 3720.00 000220 JENNIFER] LUTZ ** ** ** ** 000240 SALVATORE[ MARINO D21 28760.00 600.00 2301.00 000320 RAMLAL MEHTA ** 19950.00 400.00 1596.00 000140 HEATHER NICHOLLS C01 28420.00 600.00 2274.00 000120 SEAN O CONNELL A00 29250.00 600.00 2340.00 000290 JOHN PARKER E11 15340.00 300.00 1227.00 000270 MARIA PEREZ D21 27380.00 500.00 2190.00 000160 ELIZABETH PIANKA D11 22250.00 400.00 1780.00 000070 EVA PULASKI D21 36170.00 700.00 2893.00 000130 DOLORES, QUINTANA C01 23800.00 500.00 1904.00 000280 ETHEL/ SCHNEIDER E11 26250.00 500.00 2100.00 000180 MARILYN SCOUTTEN D11 21340.00 500.00 1707.00 000310 MAUDE SETRIGHT E11 15900.00 300.00 1272.00 000250 DANIEL SMITH D21 19180.00 400.00 1534.00 000300 PHILIP SMITH E11 17750.00 400.00 1420.00 000100 THEODORE SPENSER ** ** ** ** 000060 IRVING@ STERN D11 32250.00 500.00 2580.00 000020 MICHAEL THOMPSON ** ** ** ** 000190 JAMES& WALKER D11 20450.00 400.00 1636.00 000170 MASATOSHI% YOSHIMURA D11 24680.00 500.00 1974.00 (32 row(s) affected)
Ranking
SELECT [Salary Rank] = dense_rank() OVER (ORDER BY salary DESC) ,
EMPNO AS 'Emp#',
FIRSTNME AS 'First Name',
LASTNAME AS 'Last Name',
isnull(WORKDEPT,' **') AS 'Dept',
isnull(CAST(salary AS VARCHAR(11)),' **') AS 'Salary',
isnull(CAST(bonus AS VARCHAR(11)),' **') AS 'Bonus',
isnull(CAST(comm AS VARCHAR(11)),' **') AS 'Commission'
FROM emps
WHERE salary IS not null;
go
EMPNO AS 'Emp#',
FIRSTNME AS 'First Name',
LASTNAME AS 'Last Name',
isnull(WORKDEPT,' **') AS 'Dept',
isnull(CAST(salary AS VARCHAR(11)),' **') AS 'Salary',
isnull(CAST(bonus AS VARCHAR(11)),' **') AS 'Bonus',
isnull(CAST(comm AS VARCHAR(11)),' **') AS 'Commission'
FROM emps
WHERE salary IS not null;
go
salary rank Emp# First Name Last Name Dept Salary Bonus Commission -------------------- ------ ------------ --------------- ---- ----------- ----------- ----------- 1 000010 CHRISTINE HAAS A00 52750.00 1000.00 4220.00 2 000110 VINCENZO. LUCCHESSI A00 46500.00 900.00 3720.00 3 000050 JOHN GEYER E01 40175.00 800.00 3214.00 4 000030 SALLY! KWAN C01 38250.00 800.00 3060.00 5 000070 EVA PULASKI D21 36170.00 700.00 2893.00 6 000060 IRVING@ STERN D11 32250.00 500.00 2580.00 7 000090 EILEEN# HENDERSON E11 29750.00 600.00 2380.00 8 000120 SEAN O CONNELL A00 29250.00 600.00 2340.00 9 000240 SALVATORE[ MARINO D21 28760.00 600.00 2301.00 10 000140 HEATHER NICHOLLS C01 28420.00 600.00 2274.00 11 000200 DAVID BROWN D11 27740.00 600.00 2217.00 12 000270 MARIA PEREZ D21 27380.00 500.00 2190.00 13 000280 ETHEL/ SCHNEIDER E11 26250.00 500.00 2100.00 14 000330 WING LEE E21 25370.00 500.00 2030.00 15 000170 MASATOSHI% YOSHIMURA D11 24680.00 500.00 1974.00 16 000340 JASON GOUNOT E21 23840.00 500.00 1907.00 17 000130 DOLORES, QUINTANA C01 23800.00 500.00 1904.00 18 000160 ELIZABETH PIANKA D11 22250.00 400.00 1780.00 19 000230 JAMES JEFFERSON D21 22180.00 400.00 1774.00 20 000180 MARILYN SCOUTTEN D11 21340.00 500.00 1707.00 21 000190 JAMES& WALKER D11 20450.00 400.00 1636.00 22 000320 RAMLAL MEHTA ** 19950.00 400.00 1596.00 23 000250 DANIEL SMITH D21 19180.00 400.00 1534.00 24 000210 WILLIAM* JONES D11 18270.00 400.00 1462.00 25 000300 PHILIP SMITH E11 17750.00 400.00 1420.00 26 000260 SYBIL\ JOHNSON D21 17250.00 300.00 1380.00 27 000310 MAUDE SETRIGHT E11 15900.00 300.00 1272.00 28 000290 JOHN PARKER E11 15340.00 300.00 1227.00 (28 row(s) affected)
- The **DENSE_RANK()** function does not have any gap, it's always: 1,2,3...
- The **OVER()** window does not have to be the same as the order of the ORDER BY clause. The DENSE_RANK, it will generate it’s own sort.
- The **OVER()** order should be the same as the order of the ORDER BY clause. It’s lot more efficient, especially for large tables.
- The **OVER()** order should be covered by some index, especially if the report is a large one and often used.
Get the 3rd highest salary
- Because of the order of evaluation of the select statement, you will need to use an inline view to have the **DENSE_RANK()** in the where clause of the select statement.
- For further details and explanations of the inline view see: Aliased column in the WHERE clause, inline views with MS SQL Server
SELECT * FROM (
SELECT [Salary Rank] = dense_rank() OVER (ORDER BY salary DESC) ,
EMPNO AS 'Emp#',
FIRSTNME AS 'First Name',
LASTNAME AS 'Last Name',
isnull(WORKDEPT,' **') AS 'Dept',
isnull(CAST(salary AS VARCHAR(11)),' **') AS 'Salary',
isnull(CAST(bonus AS VARCHAR(11)),' **') AS 'Bonus',
isnull(CAST(comm AS VARCHAR(11)),' **') AS 'Commission'
FROM emps
WHERE salary IS not null
) ABC
WHERE [Salary Rank] = 3;
SELECT [Salary Rank] = dense_rank() OVER (ORDER BY salary DESC) ,
EMPNO AS 'Emp#',
FIRSTNME AS 'First Name',
LASTNAME AS 'Last Name',
isnull(WORKDEPT,' **') AS 'Dept',
isnull(CAST(salary AS VARCHAR(11)),' **') AS 'Salary',
isnull(CAST(bonus AS VARCHAR(11)),' **') AS 'Bonus',
isnull(CAST(comm AS VARCHAR(11)),' **') AS 'Commission'
FROM emps
WHERE salary IS not null
) ABC
WHERE [Salary Rank] = 3;
Salary Rank Emp# First Name Last Name Dept Salary Bonus Commission -------------------- ------ ------------ --------------- ---- ----------- ----------- ----------- 3 000050 JOHN GEYER E01 40175.00 800.00 3214.00 (1 row(s) affected)
Get the 3rd to 10th highest salary
SELECT * FROM (
SELECT [Salary Rank] = dense_rank() OVER (ORDER BY salary DESC) ,
EMPNO AS 'Emp#',
FIRSTNME AS 'First Name',
LASTNAME AS 'Last Name',
isnull(WORKDEPT,' **') AS 'Dept',
isnull(CAST(salary AS VARCHAR(11)),' **') AS 'Salary',
isnull(CAST(bonus AS VARCHAR(11)),' **') AS 'Bonus',
isnull(CAST(comm AS VARCHAR(11)),' **') AS 'Commission'
FROM emps
WHERE salary IS not null
) ABC
WHERE [Salary Rank] >= 3 and [Salary Rank] <= 10
ORDER BY [Salary Rank];
SELECT [Salary Rank] = dense_rank() OVER (ORDER BY salary DESC) ,
EMPNO AS 'Emp#',
FIRSTNME AS 'First Name',
LASTNAME AS 'Last Name',
isnull(WORKDEPT,' **') AS 'Dept',
isnull(CAST(salary AS VARCHAR(11)),' **') AS 'Salary',
isnull(CAST(bonus AS VARCHAR(11)),' **') AS 'Bonus',
isnull(CAST(comm AS VARCHAR(11)),' **') AS 'Commission'
FROM emps
WHERE salary IS not null
) ABC
WHERE [Salary Rank] >= 3 and [Salary Rank] <= 10
ORDER BY [Salary Rank];
Salary Rank Emp# First Name Last Name Dept Salary Bonus Commission -------------------- ------ ------------ --------------- ---- ----------- ----------- ----------- 3 000050 JOHN GEYER E01 40175.00 800.00 3214.00 4 000030 SALLY! KWAN C01 38250.00 800.00 3060.00 5 000070 EVA PULASKI D21 36170.00 700.00 2893.00 6 000060 IRVING@ STERN D11 32250.00 500.00 2580.00 7 000090 EILEEN# HENDERSON E11 29750.00 600.00 2380.00 8 000120 SEAN O CONNELL A00 29250.00 600.00 2340.00 9 000240 SALVATORE[ MARINO D21 28760.00 600.00 2301.00 10 000140 HEATHER NICHOLLS C01 28420.00 600.00 2274.00 (8 row(s) affected)