How to find the nth highest salary in SQL?

Question

I have found a query to find the nth highest salary from Employee table , but i don't understand the logic of (N-1)?

EmpID         Salary
1             90000
2             80000
3             54000
4             37000
5             12000
6             69000
7             50000

SELECT * FROM Employee E1
WHERE (N-1) = (
                SELECT COUNT(DISTINCT(E2.Salary))
                FROM Employee E2
                WHERE E2.Salary > E1.Salary
              )

If N= 4, then how does the query work? I'm a complete beginner in SQL, please help!

Answer 1

What's happening here is the subquery is looking at each individual salary and essentially ranking them, then comparing those salaries to the outer salary (a separate query against the same table). So in this case if you said N = 4 it is saying:

WHERE 3 = (number of salaries > outer salary)

So looking at the data you have, let's rank them in order, and compare.

EmpID   Salary   How many *distinct* salaries are greater than this one?
-----   ------   -------------------------------------------------------
5       12000    6
4       37000    5
7       50000    4
3       54000    3
6       69000    2
2       80000    1
1       90000    0

So when n = 4, the row that will be returned is EmpID 3 (54000).

A much more intuitive way to write this query, in my opinion, is to use windowing functions like RANK(), ROW_NUMBER() or DENSE_RANK() (depending on whether or not you want ties). Let's take a look at how these different functions work against your data (and I've added an 8th row to represent a tie for 4th place):

DECLARE @salary TABLE(EmpID INT, Salary INT);

INSERT @salary VALUES
(1,90000),(2,80000),(3,54000),(4,37000),
(5,12000),(6,69000),(7,50000),(8,54000);

;WITH x AS
(
  SELECT EmpID, Salary, 
    r  = RANK()       OVER (ORDER BY Salary),
    dr = DENSE_RANK() OVER (ORDER BY Salary),
    rn = ROW_NUMBER() OVER (ORDER BY Salary)
  FROM @salary
)
SELECT EmpID, Salary, r, dr, rn FROM x;

Results:

EmpID  Salary   r   dr  rn
-----  ------   --  --  --
5      12000    1   1   1
4      37000    2   2   2
7      50000    3   3   3
8      54000    4   4   4
3      54000    4   4   5
6      69000    6   5   6
2      80000    7   6   7
1      90000    8   7   8

I don't think you'd want to use RANK() for this specific problem, because of the way it works there is no 5th place, for example. So now it comes down to whether you want to include multiple rows in the case of a tie, and if not, if you want an arbitrary row or a specific row based on some criteria. So adjusting the statement slightly:

-- if you want ties:
;WITH x AS
(
  SELECT EmpID, Salary, 
    dr = DENSE_RANK() OVER (ORDER BY Salary)
  FROM @salary
)
SELECT EmpID, Salary FROM x WHERE dr = 4;

-- results:
-- 3   54000
-- 8   54000

-- to take the *lowest* EmpID:
;WITH x AS
(
  SELECT EmpID, Salary, 
    rn = ROW_NUMBER() OVER (ORDER BY Salary, EmpID)
  FROM @salary
)
SELECT EmpID, Salary FROM x WHERE rn = 4;

-- results:
-- 3   54000

-- to take the *highest* EmpID:
;WITH x AS
(
  SELECT EmpID, Salary, 
    rn = ROW_NUMBER() OVER (ORDER BY Salary, EmpID DESC)
  FROM @salary
)
SELECT EmpID, Salary FROM x WHERE rn = 4;

-- results:
-- 8   54000

Answer 2

Another way to write this query would be using the 2012+ OFFSET / FETCH syntax to find the Nth salary:

; WITH Nth AS                    -- To find the Nth highest salary,
( 
  SELECT DISTINCT Salary         -- get all the distinct salary values
  FROM Employee
  ORDER BY Salary DESC           -- order them from high to low
  OFFSET 3 ROWS                  -- skip (N-1) values
  FETCH NEXT 1 ROWS ONLY         -- and keep the next one (Nth).
)
SELECT EmpID, Salary                       -- Then show 
FROM Employee                              -- all employees that have
WHERE Salary = (SELECT Salary FROM Nth) ;  -- have a salary equal to that.

or for versions before 2012, in 2 steps. First ordering by DESC, then by ASC:

; WITH TopN AS                     -- Find the top N salaries,
(
  SELECT DISTINCT TOP (4) Salary
  FROM Employee
  ORDER BY Salary DESC
),
  Nth AS                           -- then keep only the Nth one,
( 
  SELECT TOP (1) Salary
  FROM TopN
  ORDER BY Salary
)
SELECT EmpID, Salary                       -- and show 
FROM Employee                              -- all employees that have
WHERE Salary = (SELECT Salary FROM Nth) ;  -- have a salary equal to that.

Test in SQLfiddle

Answer 3

If N=4, it returns the salary where there are 4-1=3 higher salaries, in other words, it returns the 4th highest.

Example:

Salaries (500, 400, 400, 300, 250, 200). 

Desired result is (250) (the fourth as we count '400' only once due to the DISTINCT). N-1=3 means there are 3 distinct salaries greater than 250, which are (500, 400, 300).

In your example, where there is no repeated salary, the desired result is (5400), which is the 4th highest. So, the query returns the salary where the count of salaries that are higher is 4-1.

Answer 4

Logic is very simple. We are taking two instance of same table; second in a subquery. We pick first salary of main table and compare it against all salaries in subquery table to get a count of salaries greater than the salary in main table under consideration. If count is N-1; then it implies that salary in main table is Nth max salary because there are N-1 salaries greater than this.

SELECT * FROM Employee E1
WHERE (N-1) = (
                SELECT COUNT(DISTINCT(E2.Salary))
                FROM Employee E2
                WHERE E2.Salary > E1.Salary
              )

Answer 5

PROPOSED MySQL QUERY

To get the 5th Largest Salary

SET @nth = 5;
SET @ndx = 0;
SELECT @nth nth,EmpID,salary FROM
(
    SELECT (@ndx:=@ndx+1) ndx,EmpID,salary
    FROM employee ORDER BY salary DESC
) A WHERE ndx = @nth;

SAMPLE DATA

DROP DATABASE IF EXISTS thinker2305;
CREATE DATABASE thinker2305;
USE thinker2305
CREATE TABLE employee
(EmpID int not null auto_increment primary key,
salary int not null);
INSERT INTO employee (salary) values
(90000),(80000),(54000),(37000),
(12000),(69000),(50000);
SELECT * FROM employee ORDER BY salary DESC;

SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS thinker2305;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE thinker2305;
Query OK, 1 row affected (0.00 sec)

mysql> USE thinker2305
Database changed
mysql> CREATE TABLE employee
    -> (EmpID int not null auto_increment primary key,
    -> salary int not null);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO employee (salary) values
    -> (90000),(80000),(54000),(37000),
    -> (12000),(69000),(50000);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM employee ORDER BY salary DESC;
+-------+--------+
| EmpID | salary |
+-------+--------+
|     1 |  90000 |
|     2 |  80000 |
|     6 |  69000 |
|     3 |  54000 |
|     7 |  50000 |
|     4 |  37000 |
|     5 |  12000 |
+-------+--------+
7 rows in set (0.00 sec)

mysql>

PROPOSED MySQL QUERY EXECUTED (5th Largest)

mysql> SET @nth = 5;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @ndx = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @nth nth,EmpID,salary FROM
    -> (
    ->     SELECT (@ndx:=@ndx+1) ndx,EmpID,salary
    ->     FROM employee ORDER BY salary DESC
    -> ) A WHERE ndx = @nth;
+------+-------+--------+
| nth  | EmpID | salary |
+------+-------+--------+
|    5 |     7 |  50000 |
+------+-------+--------+
1 row in set (0.00 sec)

mysql>

PROPOSED MySQL QUERY EXECUTED (3rd Largest)

mysql> SET @nth = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @ndx = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @nth nth,EmpID,salary FROM
    -> (
    ->     SELECT (@ndx:=@ndx+1) ndx,EmpID,salary
    ->     FROM employee ORDER BY salary DESC
    -> ) A WHERE ndx = @nth;
+------+-------+--------+
| nth  | EmpID | salary |
+------+-------+--------+
|    3 |     6 |  69000 |
+------+-------+--------+
1 row in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

Answer 6

Declare @nth varchar(4) = '10' ;
Declare @inner varchar(max) = 
'Select top ' + @nth  + ' * from employee     order by salary desc'
Declare @outer varchar(max) = 
'Select top 1 * from (' + @inner +') a order by salary';

--exec (@inner)
exec (@outer);

This is how I approached this with TSQL. I half-thought about paramaterizing the order by as well so it could flip and do top nth or bottom nth.

Answer 7

-- Another way with TSQL

declare @n

set @n = 9  -- example for nth position

set @n=@n -1

select top 1 * from Employees 
where Salary not in (select top (@n) salary from Employees order by Salary desc)
order by Salary desc