# How to find the nth highest salary in SQL?

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!

• What if you have a tie? Do you want a random row, the one with the lowest EmpID, some other criteria to break the tie, or do you want all rows that tie for 4th? Apr 30, 2015 at 13:24
• Why is this tagged with both `[sql-server]` and `[mysql]`? Which of the two are you using? Apr 30, 2015 at 17:27

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
``````

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

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.

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
)
``````
• "Simple", but "slow". For 1000 employees, it will take 1000000 'operations' to get the answer. Jan 19, 2020 at 22:55

# 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;
``````

``````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 !!!

``````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.

-- 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
``````