Second highest salary

Table employees(id, name, salary). Write a query returning the second highest distinct salary (NULL if none).

This is the classic SQL interview question, and it has a trap in it: duplicates and the "none" case.

Say the table looks like this:

id	name	salary
1	Asha	90000
2	Ravi	75000
3	Meena	90000
4	Kiran	60000

The highest salary is 90,000 (twice). The second highest distinct salary is 75,000, not 90,000 again. And if the table had only one person, or everyone earned the same, the answer should be NULL, not an empty result.

Starter code

SELECT ...

💡 Hint 1

Use a subquery with MAX

💡 Hint 2

Or ORDER BY with LIMIT/OFFSET

✅ Show solution

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

How it works

The inner query finds the highest salary (90,000). The outer query looks at everything below that and takes the maximum, which gives 75,000. Because MAX() over zero rows returns NULL, the "only one person" case works on its own.

The LIMIT/OFFSET version

You'll often see this:

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

It's fine, but it has two gotchas. Without DISTINCT, the duplicate 90,000 sits in second place. And if there's no second salary, it returns no rows rather than NULL. To fix that, wrap it:

SELECT (SELECT DISTINCT salary FROM employees
        ORDER BY salary DESC LIMIT 1 OFFSET 1) AS second_highest;

For "Nth highest"

When they ask for the 3rd or 5th highest, switch to a window function:

SELECT DISTINCT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t WHERE rnk = 3;

DENSE_RANK gives tied salaries the same rank without skipping numbers, which is exactly what "Nth highest distinct" means. Window functions need MySQL 8 or newer.

Tip: run your code in a local Python environment, Jupyter or Google Colab.