chaturIQ
Sign in
Thinking habit: Decide what a tie means

Find the Nth highest salary in SQL (ROW_NUMBER vs DENSE_RANK)

The question: Return the third highest salary. People who earn the same amount share a place, so count distinct amounts.

employees

emp_idnamesalary
1Asha90000
2Ravi90000
3Neha80000
4Kabir70000
5Meera70000
6Arjun50000

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

Show the answer and why the first query is wrong

Why the usual answer is wrong

SELECT salary AS third_highest
FROM (
  SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees
)
WHERE rn = 3;

ROW_NUMBER numbers rows, not amounts: Asha and Ravi take places 1 and 2, so place 3 is Neha's 80,000. That's the second highest amount, not the third.

The answer

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

DENSE_RANK gives equal salaries the same rank and leaves no gaps: 90,000 is 1, 80,000 is 2, 70,000 is 3. DISTINCT keeps one row when two people share that salary.

Change 3 to any N. RANK would leave gaps (1, 1, 3, …), so it answers a different question: the salary of the third person.

With DISTINCT and OFFSET

SELECT DISTINCT salary AS third_highest
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 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