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
- Ask: if two people tie for the highest salary, is the "second highest" the next different amount? (Usually yes.)
- Rank the distinct salaries from highest down, so ties share a rank and no number is skipped.
- Keep the salary ranked 2.
- 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
- Ties for first: two people on 95000 — the answer is still the next different salary, 80000.
- Fewer than two different salaries: return NULL, not an empty result.
- NULL salaries: ignored by ranking and MAX, which is usually what you want — say so.
Common mistakes
ORDER BY salary DESC LIMIT 1 OFFSET 1without DISTINCT: returns the top salary again when two people tie for first.- Using RANK: after a tie for first, there is no rank 2.
- Returning an empty result when the question asked for NULL.
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.