Second highest salary in SQL: the answer that survives a tie

LIMIT 1 OFFSET 1 fails the interview. It is the first answer everyone writes for "find the second highest salary", and it is only right when every salary is unique. The question exists to see whether you think about ties, NULLs and tables with one row. One page on why the naive answer breaks and the three answers that hold. The print-ready A4 PDF is at the bottom.

The question

• Find the second highest salary in the employees table.
• What it really tests: ties, NULLs, and one-row tables.
• Ask first: the second distinct value, or the second person?

The naive answer

• ORDER BY salary DESC LIMIT 1 OFFSET 1.
• It works when every salary is unique.
• And when the table has at least two rows.

Why it breaks

• A tie at the top: it returns the top salary again.
• One row only: it returns no row, not NULL.
• OFFSET skips rows, not values.

Robust answers

• DENSE_RANK() = 2: tied salaries share a rank.
• MAX below MAX: the highest salary under the highest, NULL if there is no second one.
• DISTINCT plus OFFSET: correct on ties, and wrapped in a scalar subquery it returns NULL when empty.

-- MAX below MAX
SELECT MAX(salary) AS second_highest
  FROM employees
 WHERE salary < (SELECT MAX(salary) FROM employees);

-- DISTINCT + OFFSET, wrapped so an empty result is NULL
SELECT (
  SELECT DISTINCT salary
    FROM employees
   ORDER BY salary DESC
   LIMIT 1 OFFSET 1
) AS second_highest;

Nth, per department

• Rank = n: the same query works for any n.
• PARTITION BY department_id: the second highest in each team.
• MAX below MAX stops scaling at n = 3: one more nested subquery per step.

The answer that survives a tie

-- second highest distinct salary, NULL if none
SELECT MAX(salary) AS second_highest
  FROM (
    SELECT salary, DENSE_RANK() OVER (
      ORDER BY salary DESC NULLS LAST) AS rk
      FROM employees
  ) ranked
 WHERE rk = 2;

-- nth highest: change the 2
-- per department: PARTITION BY department_id

Rank the salary values, not the rows. MAX() around the result does two jobs: several people on rank 2 collapse to one value, and a table with a single salary returns NULL instead of zero rows.

Gotchas

• RANK() leaves gaps: after a tie at the top there is no rank 2.
• ROW_NUMBER() splits ties arbitrarily, so it fails like OFFSET.
• A NULL salary sorts first in ORDER BY ... DESC on Postgres: add NULLS LAST.

The trap: same question, three tables

The DENSE_RANK column is the query above, with MAX() and NULLS LAST, on PostgreSQL.

The quiz

Which one survives a tie?

A) SELECT salary FROM employees
   ORDER BY salary DESC
   LIMIT 1 OFFSET 1

B) SELECT DISTINCT salary FROM (
   SELECT salary, DENSE_RANK()
   OVER (ORDER BY salary DESC) AS r
   FROM employees) t WHERE r = 2

B. When two people tie at the top, A skips one row and returns the top salary again. DENSE_RANK gives both of them rank 1, so rank 2 is the next distinct salary.

Interview phrasing worth memorizing: do you want the second distinct salary, and what should come back when there is none? Then I rank with DENSE_RANK.