SQL practice › SQL reference › Second highest salary
SQL second highest salary: three queries, and what ties do to each
The shortest answer is the highest salary below the highest one: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). With LIMIT it is SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1. With a window function, you number the salaries with DENSE_RANK() and keep number 2.
On the table below all three find the same salary. They differ in what they do when two people earn the same, in how easily they stretch to the third or the Nth, and in whether they can tell you who earns it. The word that decides most of it is DISTINCT.
Syntax
SELECT name, salary
FROM (
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS pos
FROM employees
) ranked
WHERE pos = 2DENSE_RANK() OVER (ORDER BY salary DESC)- Numbers the salaries from the highest down. Equal salaries get the same number, and the next salary gets the next number, with no gap.
FROM ( … ) ranked- The numbering is done in a subquery, because a window function can't be used in
WHERE. The outer query then filters on its result like on any column. WHERE pos = 2- 2 for the second highest, 3 for the third, N for the Nth. Everyone who earns that salary is returned.
The table
Nine employees in three departments. Two pairs earn the same: Zoe and Kim (88000), Omar and Lena (58000). From the top, the salaries read 95000, 88000, 88000, 81000.
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
1. The highest salary below the highest
No sorting and no numbering: find the top salary, then the top of what is left under it.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| MAX(salary) |
|---|
| 88000 |
2. ORDER BY with LIMIT and OFFSET
Sort the salaries from the top, skip one, take one. OFFSET says how many lines to skip and LIMIT how many to return.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| salary |
|---|
| 88000 |
DISTINCT turns the list into one line per salary, so skipping one line means skipping one salary. For the Nth highest, skip N - 1: OFFSET 2 gives the third.The trap: leaving DISTINCT out. Without it, the sorted list has one line per employee, and OFFSET skips people, not salaries. As long as one person holds the top salary, nothing looks wrong. Take the six employees who have a manager: two of them, Zoe and Kim, share the top salary of that group.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| salary |
|---|
| 88000 |
SELECT DISTINCT salary and run it again.What does "second highest" mean when there is a tie?
If two people share the top salary, "the second highest salary" can be read two ways: the salary of the second person in the list (the same amount again), or the next amount below. This page takes it to mean the second highest distinct amount, which is what DISTINCT, DENSE_RANK() and the MAX-below-MAX query all give. In an interview, say which reading you are answering before you write the query.
The three numbering functions make the difference visible. They only disagree on ties.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | salary | row_num | rnk | dense |
|---|---|---|---|---|
| Raj | 95000 | 1 | 1 | 1 |
| Zoe | 88000 | 2 | 2 | 2 |
| Kim | 88000 | 3 | 2 | 2 |
| Ivan | 81000 | 4 | 4 | 3 |
| Maya | 72000 | 5 | 5 | 4 |
| Omar | 58000 | 6 | 6 | 5 |
| Lena | 58000 | 7 | 6 | 5 |
| Nora | 46000 | 8 | 8 | 6 |
| Theo | 43000 | 9 | 9 | 7 |
ROW_NUMBER gives the two 88000s different numbers, 2 and 3, and nothing in the query decides which of them gets which. RANK gives both 2 and then jumps to 4: nobody is third. DENSE_RANK gives both 2 and Ivan 3. Only the last column counts salaries the way the question does.3. DENSE_RANK: the salary and who earns it
The first two queries return an amount. To get the people, number the rows and keep the number you want.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | salary |
|---|---|
| Zoe | 88000 |
| Kim | 88000 |
LIMIT 1 query could only have returned one of them. Change the 2 to any N for the Nth highest salary.The second highest in each group
Add PARTITION BY and the numbering starts again at 1 in every department. The rest of the query stays the same.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| department | name | salary |
|---|---|---|
| Engineering | Zoe | 88000 |
| Engineering | Kim | 88000 |
| Sales | Omar | 58000 |
| Sales | Lena | 58000 |
| Support | Theo | 43000 |
Practice: 4 exercises
Write the query in the box and press Run. Your result is compared with the right one, on this table and again on changed data where the ties sit elsewhere, so a query that is only right by luck won't pass. Column names don't matter; the columns and rows do.
Return the second lowest salary in the company: one number. If several people share the lowest salary, the answer is the next amount above it.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT MIN(salary)
FROM employees
WHERE salary > (SELECT MIN(salary) FROM employees)| MIN(salary) |
|---|
| 46000 |
The page's first query, mirrored: the lowest salary above the lowest. 43000 is the lowest, so the answer is 46000. SELECT DISTINCT salary FROM employees ORDER BY salary LIMIT 1 OFFSET 1 gets the same result; without DISTINCT it would break as soon as two people shared the lowest salary.
Return the third highest salary in the company: one number. Equal salaries count once.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2| salary |
|---|
| 81000 |
The distinct salaries from the top are 95000, 88000, 81000. Skipping two of them leaves 81000. Without DISTINCT the same query returns 88000, because Zoe and Kim fill the second and the third line.
Leave out the three department heads (their manager_id is NULL). Among the other employees, return the name and salary of everyone who earns the second highest salary.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT name, salary
FROM (
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS pos
FROM employees
WHERE manager_id IS NOT NULL
) ranked
WHERE pos = 2| name | salary |
|---|---|
| Ivan | 81000 |
Without the heads, Zoe and Kim share the top salary (88000), so the second highest is Ivan's 81000. The WHERE manager_id IS NOT NULL goes inside the subquery: the salaries have to be numbered after the heads are removed. RANK() would number this group 1, 1, 3 and return nothing for 2.
For each department, return the department and its second highest salary: one row per department, even when two people earn that salary. A department where everyone earns the same has no second highest salary and doesn't appear.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT department, MAX(salary)
FROM employees e
WHERE salary < (SELECT MAX(salary) FROM employees WHERE department = e.department)
GROUP BY department| department | MAX(salary) |
|---|---|
| Engineering | 88000 |
| Sales | 58000 |
| Support | 43000 |
For each row, the inner query finds the top salary of that row's own department. The WHERE keeps the rows below it, and MAX with GROUP BY takes the highest of what is left in each department: 88000, 58000 and 43000. The window version works too: the query from the last example, with SELECT DISTINCT department, salary.
Questions people ask
How do I find the Nth highest salary in SQL?
Use the DENSE_RANK() query and filter on pos = N, or sort the distinct salaries and skip N - 1 of them: SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET N - 1, with the number written out. The first also returns who earns it. The MAX-below-MAX query needs one more nested subquery for every step down, so it stops being practical after the second.
How do I get the second highest salary using LIMIT?
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1. SQLite, PostgreSQL and MySQL all accept LIMIT 1 OFFSET 1. MySQL and SQLite also accept LIMIT 1, 1, where the offset comes first; SQLite's documentation advises the form with the word OFFSET. SQL Server writes the same idea as ORDER BY salary DESC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY.
What if there is no second highest salary?
It depends on the query. The MAX-below-MAX query always returns one row, and the value is NULL when no salary is below the top. The LIMIT and DENSE_RANK() queries return no rows at all. Neither is wrong, but say which one your query does, and wrap the LIMIT query in a subquery, SELECT (SELECT DISTINCT salary … LIMIT 1 OFFSET 1), if a NULL is required.
Why not use ROW_NUMBER() or RANK()?
They behave differently on ties, and this question is about ties. ROW_NUMBER() gives two equal salaries two different numbers, so "number 2" can be the top salary again. RANK() gives them the same number and then skips one, so after a tie at the top there is no number 2. DENSE_RANK() counts distinct salaries, which is what "second highest" asks for. The side-by-side query above shows all three.
Why can't I write WHERE DENSE_RANK() OVER (…) = 2?
Window functions are computed after WHERE has filtered the rows, so they are only allowed in the SELECT list and in ORDER BY. SQLite answers with "misuse of window function". Compute the number in a subquery or a CTE, and filter on it in the outer query.
How do I find the second highest value for each group?
Add PARTITION BY to the window: DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) numbers the salaries inside each department, and WHERE pos = 2 keeps the second of each. Without window functions, compare each row with the top of its own group, as in the answer to the last exercise.