Second highest salary in SQL: queries that handle ties

To find the second highest salary in SQL, take the largest salary below the maximum: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). The query runs in every major database, copes with two people tied for the top salary, and returns NULL when no second salary exists. The shorter ORDER BY salary DESC LIMIT 1 OFFSET 1 returns the top salary again whenever the top salary is shared.

Why does LIMIT 1 OFFSET 1 return the wrong second highest salary?

ORDER BY salary DESC LIMIT 1 OFFSET 1 skips one row, not one salary. When two employees share the top salary, the second row still holds the top salary. Ava and Liam both earn 9000 here, so the query answers 9000:

WITH employees(name, salary) AS (
  VALUES ('Ava', 9000), ('Liam', 9000), ('Noah', 7000), ('Zoe', 7000), ('Mia', 6500)
)
SELECT salary AS second_highest
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

second_highest
9000

The second highest salary is 7000. Interviewers put a tie in their test data for exactly this reason, so a query that passes on unique salaries fails the grader. The other usual mistakes are forgetting that the table may hold only one distinct salary, and NULL salaries, both covered below.

How do I find the second highest salary with a subquery?

The subquery form finds the maximum first, then takes the maximum of every salary below it. Ties at the top cannot fool it, because salary < 9000 removes every 9000 at once:

WITH employees(name, salary) AS (
  VALUES ('Ava', 9000), ('Liam', 9000), ('Noah', 7000), ('Zoe', 7000), ('Mia', 6500)
)
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

second_highest
7000

Use MAX(salary) WHERE salary < (SELECT MAX(salary) ...) when you need one value and the database is unknown. It needs no window functions and no LIMIT, so it works on old MySQL, SQL Server and Oracle alike. Its weakness is the third or fourth highest salary, where the subqueries nest one level deeper each time.

How do I fix LIMIT and OFFSET with DISTINCT?

SELECT DISTINCT salary collapses the tied salaries into one row each, so OFFSET 1 then skips one salary instead of one employee:

WITH employees(name, salary) AS (
  VALUES ('Ava', 9000), ('Liam', 9000), ('Noah', 7000), ('Zoe', 7000), ('Mia', 6500)
)
SELECT DISTINCT salary AS second_highest
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

second_highest
7000

MySQL and PostgreSQL accept LIMIT 1 OFFSET 1, and MySQL also accepts the older LIMIT 1, 1. SQL Server 2012 and later and Oracle 12c and later have no LIMIT; write OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY after the ORDER BY instead.

How do I find the employee with the second highest salary?

DENSE_RANK() gives every salary a rank with no gaps after a tie, so rank 2 is the second highest salary, and filtering on it returns every employee who earns it. Both Noah and Zoe earn 7000, and both come back:

WITH employees(name, salary) AS (
  VALUES ('Ava', 9000), ('Liam', 9000), ('Noah', 7000), ('Zoe', 7000), ('Mia', 6500)
),
ranked AS (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT name, salary
FROM ranked
WHERE rnk = 2
ORDER BY name;

The filter sits in an outer query because a window function is not allowed in the WHERE of the query that computes it. The same pattern, with PARTITION BY added, picks the top row in each group, as in the GROUP BY error post.

Why does RANK() = 2 return no rows?

RANK() leaves a gap after a tie. Ava and Liam both get rank 1, so the next salary gets rank 3, and no row has rank 2. Swapping DENSE_RANK for RANK in the query above returns nothing:

WITH employees(name, salary) AS (
  VALUES ('Ava', 9000), ('Liam', 9000), ('Noah', 7000), ('Zoe', 7000), ('Mia', 6500)
),
ranked AS (
  SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT name, salary
FROM ranked
WHERE rnk = 2
ORDER BY name;

name	salary

ROW_NUMBER() fails the other way: it numbers the two 9000 rows 1 and 2, so rn = 2 returns Ava or Liam, with 9000. For “the Nth highest salary”, only DENSE_RANK counts salaries rather than rows.

How do I return NULL when there is no second highest salary?

Wrap the query in a scalar subquery. A query that finds no row returns an empty result, but a subquery used as a value turns “no row” into NULL. Interview graders, LeetCode’s Second Highest Salary problem among them, expect one row holding NULL when every employee earns the same:

WITH employees(name, salary) AS (
  VALUES ('Ava', 9000), ('Liam', 9000)
)
SELECT (
  SELECT DISTINCT salary
  FROM employees
  ORDER BY salary DESC
  LIMIT 1 OFFSET 1
) AS second_highest;

second_highest
NULL

The MAX subquery form needs no wrapper: MAX over no rows is already NULL, so it returns one row holding NULL on the same data.

How do I find the Nth highest salary?

Change the number in the DENSE_RANK filter. rnk = 3 gives the third highest salary, and the scalar subquery around it still returns NULL when fewer than three distinct salaries exist:

WITH employees(name, salary) AS (
  VALUES ('Ava', 9000), ('Liam', 9000), ('Noah', 7000), ('Zoe', 7000), ('Mia', 6500)
)
SELECT (
  SELECT DISTINCT salary
  FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
  ) AS ranked
  WHERE rnk = 3
) AS third_highest;

third_highest
6500

With DISTINCT and OFFSET, the Nth highest salary is OFFSET N - 1. In a MySQL 8 stored function, compute N - 1 into a variable first, because MySQL does not accept an expression after LIMIT or OFFSET.

What if some salaries are NULL?

MAX skips NULL salaries, so the subquery form is safe. ORDER BY salary DESC is not, in LIMIT queries or inside DENSE_RANK: PostgreSQL sorts NULLs as larger than any value, so in a descending sort they come first, and a NULL can take the first or second place. Filter them out, and every form gives the same answer on every database:

WITH employees(name, salary) AS (
  VALUES ('Ava', 9000), ('Liam', NULL), ('Noah', 7000), ('Mia', 6500)
)
SELECT DISTINCT salary AS second_highest
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

second_highest
7000

The PostgreSQL manual on sorting rows documents the NULL order and NULLS LAST, the manual on LIMIT and OFFSET explains why OFFSET needs an ORDER BY, and the window functions reference defines rank and dense_rank.

Which query should I write in an interview?

Write the MAX subquery first, because it is short and correct on ties, then offer DENSE_RANK when the interviewer asks for the employee names or the Nth highest salary. Say out loud how each one handles a tie, a single distinct salary and NULLs: those three cases are what the follow-up questions test. The SQL interview practice guide lists more questions of the same shape, and the DataLemur post works through another common wrong answer, COUNT(*) after a LEFT JOIN.

Related questions

The second highest salary in each department adds PARTITION BY department to the DENSE_RANK query and has pitfalls of its own. Top N per group, and when to choose RANK, DENSE_RANK or ROW_NUMBER, are separate questions with answers of their own.