Second Highest Salary: An Interview Question With Four Wrong Answers

LIMIT 1 OFFSET 1 looks perfectly sensible. It breaks the moment two people earn the same — and they always do.

The problem

Find the second highest salary. This is LeetCode 176 — the most asked SQL interview question there is.

The common wrong answer

SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

If two people earn 5000, this returns 5000 again — but the question asks for the second highest salary, which is 4000.

The correct answer: DENSE_RANK

WITH ranked AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT DISTINCT salary FROM ranked WHERE rnk = 2;

Knowing the three functions apart is what the interviewer is actually marking:

• ROW_NUMBER — 1, 2, 3, 4. Always unique, breaks ties arbitrarily.
• RANK — 1, 1, 3, 4. Ties, then skips.
• DENSE_RANK — 1, 1, 2, 3. Ties, no gaps.

“Nth highest salary” is always DENSE_RANK.

The follow-up: what if there is no Nth rank?

The query above returns no rows. LeetCode expects NULL. Wrap it one level:

SELECT (
  SELECT DISTINCT salary FROM ranked WHERE rnk = 2
) AS second_highest_salary;

A scalar subquery with no rows yields NULL rather than an empty result. This detail fails a lot of people on problem 176.

Common mistakes

1. Filtering the window function in WHERE

-- Syntax error
WHERE DENSE_RANK() OVER (ORDER BY salary DESC) = 2

Window functions run after WHERE. You must wrap them in a CTE or subquery.

2. Forgetting DISTINCT

Two people at rank 2 produce two identical rows.

3. Not handling NULL in ORDER BY

PostgreSQL puts NULL first on a DESC sort. Add ORDER BY salary DESC NULLS LAST if the column is nullable.

Practice

• LeetCode 176 — Second Highest Salary
• LeetCode 177 — Nth Highest Salary
• LeetCode 178 — Rank Scores
• LeetCode 184 — Department Highest Salary
• HackerRank — Top Competitors