Write a query to find the Nth highest salary in a table, where N is a parameter, handling ties correctly.

How to think about it

This is a classic ranking question, and the trap is sitting in the data: what happens when two people share the top salary? The interviewer wants to see whether you reach for DENSE_RANK (correct) or reflexively grab RANK or LIMIT/OFFSET (wrong under ties).

Three ranking functions differ only in how they treat ties:

Function	Ties	Gap after a tie?
ROW_NUMBER()	arbitrary tiebreak, every row unique	no
RANK()	tied rows share a rank, next rank skips	yes — 1, 1, 3
DENSE_RANK()	tied rows share a rank, next is consecutive	no — 1, 1, 2

For “Nth highest salary” you need DENSE_RANK: with two people tied at rank 1, RANK jumps straight to 3 and “rank 2” never exists.

A worked example

The data has a tie at the top (two on 120,000) and a tie at the bottom (two on 72,000). Watch how DENSE_RANK numbers the distinct salary levels:

SELECT id, name, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
ORDER BY rnk, id;

id	name	salary	rnk
1	Aarav	120000	1
2	Bea	120000	1
3	Chen	95000	2
4	Dara	80000	3
5	Eli	72000	4
6	Farah	72000	4

Both 120,000 earners share rank 1, and the next salary (95,000) is rank 2, not 3 — no gap. So the 2nd-highest salary is found by filtering that rank:

SELECT id, name, salary, rnk
FROM (
  SELECT id, name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
WHERE rnk = 2;

id	name	salary	rnk
3	Chen	95000	2

Change rnk = 2 to any N for the Nth-highest. Had you used RANK() here, the 95,000 row would carry rank 3, and WHERE rnk = 2 would return zero rows even though a clear second-highest salary exists.

Why LIMIT/OFFSET is wrong

-- fragile: dedupe-then-skip, breaks when the (N-1)th salary is tied
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

DISTINCT + OFFSET can accidentally give the right answer on clean data, but it expresses no intent and stumbles on tie patterns. DENSE_RANK says exactly what you mean. It also degrades gracefully: filter WHERE rnk = 10 when only 4 distinct salaries exist and you get zero rows — a clean signal to the caller that N is out of range.