chaturIQ
Sign in
Thinking habit: Question the number

Find the second highest salary in SQL

The question: Return the second highest salary in the company. If two people share the top salary, the second highest is the next lower amount.

employees

emp_idnamesalary
1Asha90000
2Ravi90000
3Neha70000
4Kabir70000
5Meera50000
6ArjunNULL

This query looks right. Run it, decide if it is, and fix it.

SELECT salary AS second_highest
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
Show the answer and why the first query is wrong

Why the usual answer is wrong

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

Asha and Ravi both earn 90,000, so skipping one row lands on the other 90,000. The question asks for the second highest amount, not the second row.

The answer

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

Find the highest salary first, then take the highest salary below it. Ties at the top can't fool this, and NULL salaries are ignored by MAX.

Interviewers often follow up with "now the Nth highest": use DENSE_RANK, which gives tied salaries the same rank.

With DENSE_RANK (works for any N)

SELECT DISTINCT salary AS second_highest
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
  WHERE salary IS NOT NULL
)
WHERE rnk = 2;

Practise more like this, on real business data

324 interview-style questions on 35 tables with the duplicates, NULLs and missing weeks interviewers test. Free.

Open the lesson

More SQL interview questions