Advanced · Lesson 15 of 17

Second highest salary

The classic interview question, solved three ways.

Why interviewers love it

It looks simple, but it tests ties, empty results, and whether you know window functions. Ask what should happen when two people share the top salary before you write anything.

Pick the method that fits

A subquery is short and works everywhere. DENSE_RANK handles ties cleanly and extends to the Nth highest. LIMIT with OFFSET is quick, but repeated top salaries can trip it up unless you use DISTINCT.

Examples

Subquery

Returns NULL if there is no second value, which is usually the right answer.

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

DENSE_RANK

Change 2 to any N for the Nth highest.

SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) t WHERE rnk = 2 LIMIT 1;

DISTINCT with OFFSET

DISTINCT stops ties at the top from counting as the second place.

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