How do you find the second-highest salary in SQL?
Rank salaries with DENSE_RANK() ordered descending and keep rank 2 — it handles duplicate salaries and generalises to the Nth-highest. A correlated subquery (the max salary strictly below the overall max) also works, while LIMIT 1 OFFSET 1 is only safe when no two people share a salary.
How to think about it
The interviewer is really checking two things: do you reach for a window function, and do you handle ties correctly. Most candidates write LIMIT 1 OFFSET 1 and quietly get it wrong on tied data.
The robust answer is DENSE_RANK: it assigns 1 to the highest salary, 2 to the next distinct salary, and never skips a number when values tie. Swap rnk = 2 for rnk = N and you have the Nth-highest with no rewrite — which is exactly the follow-up they ask next.
A worked example
Two employees tie at the top on 120,000; the second-highest salary is therefore 95,000, and DENSE_RANK = 2 finds it:
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = 2;| salary |
|---|
| 95000 |
Both 120,000 earners share rank 1, so rank 2 is the next distinct salary — 95,000 — not the third row. RANK would also give 95,000 here, but on a query asking for rnk = 2 after the tie it can return nothing; DENSE_RANK never gaps, so it’s the safe default.
The no-window-function answer
If window functions are banned (older MySQL, or to test fundamentals), a correlated subquery reads cleanly as “the largest salary below the largest salary”:
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);| second_highest |
|---|
| 95000 |
Same answer — and it returns NULL rather than erroring when no second salary exists, which many interviewers consider the correct behaviour and a good thing to say out loud. So: lead with DENSE_RANK, name the tie behaviour explicitly (that’s the whole point), and confirm the empty case — what should the query return if everyone earns the same?