How do you find the Nth highest salary?

Question

Accepted Answer

The classic SQL puzzle. Several common approaches. LIMIT + OFFSET — ORDER BY salary DESC LIMIT 1 OFFSET N-1 ; simplest but doesn't handle ties. DENSE_RANK() — window function; WHERE rank = N handles tied salaries correctly. The standard modern answer (MySQL 8+). Correlated subquery — classic interview trick: WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM emp WHERE salary > e.salary) . RANK vs DENSE_RANK — matters when ties exist; DENSE_RANK assigns 1, 2, 3 without gaps. This is THE most asked SQL interview question. Show all three methods: LIMIT OFFSET (simple), subquery (classic), DENSE_RANK (best — handles ties). DENSE_RANK vs RANK: DENSE_RANK has no gaps (1,2,2,3). RANK has gaps (1,2,2,4). The 'per department' variant using PARTITION BY is the common follow-up.