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.

Skip to the 4 exercises ↓

Syntax

SELECT name, salary
FROM (
  SELECT name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS pos
  FROM employees
) ranked
WHERE pos = 2
DENSE_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.

employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-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.

Live queryMAX below the MAX
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 1 row
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.

Live queryDISTINCT, then skip one
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 1 row
salary
88000

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.

Live queryTwo people at the top: OFFSET 1 lands on the second of them
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 1 row
salary
88000

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.

Live queryROW_NUMBER, RANK and DENSE_RANK side by side
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 9 rows
namesalaryrow_numrnkdense
Raj95000111
Zoe88000222
Kim88000322
Ivan81000443
Maya72000554
Omar58000665
Lena58000765
Nora46000886
Theo43000997

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.

Live queryEveryone who earns the second highest salary
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 2 rows
namesalary
Zoe88000
Kim88000

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.

Live querySecond highest salary per department
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 5 rows
departmentnamesalary
EngineeringZoe88000
EngineeringKim88000
SalesOmar58000
SalesLena58000
SupportTheo43000

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.

Exercise 1The second lowest salary

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT MIN(salary)
FROM employees
WHERE salary > (SELECT MIN(salary) FROM employees)
Result · 1 row
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.

Exercise 2The third highest salary

Return the third highest salary in the company: one number. Equal salaries count once.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2
Result · 1 row
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.

Exercise 3Second highest among the people who have a manager

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
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
Result · 1 row
namesalary
Ivan81000

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.

Exercise 4Second highest salary of each department

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT department, MAX(salary)
FROM employees e
WHERE salary < (SELECT MAX(salary) FROM employees WHERE department = e.department)
GROUP BY department
Result · 3 rows
departmentMAX(salary)
Engineering88000
Sales58000
Support43000

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.

Keep going

Ten questions on a real database, from SELECT to JOINs and subqueries. Free, no sign-up.

Related

SQL interview questions, with answersRANK vs DENSE_RANK vs ROW_NUMBER, side by sideSQL top N per group, with live queriesSQL GROUP BY, with live queriesSQL skills testSQL reference with queries you can runSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: SQLite documentation: SELECT (DISTINCT, LIMIT and OFFSET), SQLite documentation: window functions, SQLite documentation: aggregate functions, PostgreSQL documentation: LIMIT and OFFSET, PostgreSQL documentation: window functions (tutorial), MySQL reference manual: SELECT (LIMIT), SQL Server documentation: ORDER BY clause (OFFSET and FETCH). Last checked November 2026.