The second highest salary (and the Nth)

Return the second highest salary from employees. If there is no second highest salary, return NULL.

What it tests

Whether you think about ties and about "no answer". LIMIT 1 OFFSET 1 is wrong when two people share the top salary, and many answers return no row at all instead of NULL.

The pattern

Nth highest value. The words "second highest", "Nth largest", "third most recent". The answer is a value, not a person, so ties must collapse: think DENSE_RANK or DISTINCT.

How to approach it

  1. Ask: if two people tie for the highest salary, is the "second highest" the next different amount? (Usually yes.)
  2. Rank the distinct salaries from highest down, so ties share a rank and no number is skipped.
  3. Keep the salary ranked 2.
  4. Wrap it so that "no such salary" gives one row containing NULL, not an empty result — an aggregate like MAX does this.

Edge cases to mention

Common mistakes

Not true: ORDER BY salary DESC LIMIT 1 OFFSET 1: the second highest salary, ties or no ties.
In fact: When two people tie for first place, the second row is the top salary again. Add DISTINCT, or use DENSE_RANK.

Part of Unit 8: SQL interview problems, from easy to hard in the free SQL course.