Skip to content
AnswerInterview
Menu

How do you find the second highest salary in SQL?

Short answer: Rank distinct salaries with DENSE_RANK and pick rank 2, or take the maximum salary below the overall maximum.

What to cover

  • Use DENSE_RANK so ties at the top don't skip the real second value.
  • Alternative: MAX(salary) WHERE salary < (SELECT MAX(salary)).
  • Say what happens with fewer than two distinct salaries (returns no row / NULL).
  • Mention LIMIT/OFFSET works too but needs DISTINCT.

Example answer

I'd use DENSE_RANK over salary descending and filter for rank 2. DENSE_RANK gives tied salaries the same rank, so if two people share the top salary, the next distinct salary is still rank 2. A simpler alternative is the maximum salary below the overall maximum.

Code

-- Works in MySQL 8+ and PostgreSQL
SELECT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2
LIMIT 1;

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

Avoid

  • ROW_NUMBER without handling ties.
  • LIMIT 1 OFFSET 1 without DISTINCT.
In a live interview, AnswerInterview shows an answer like this about 2 seconds after the question — and corrects you if you slip up. Try 30 minutes free (₹349 Week Pass after).

Related questions