How do you find the Nth highest salary?
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 = Nhandles 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_RANKassigns 1, 2, 3 without gaps.
-- Method 1: LIMIT OFFSET (2nd highest)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1; -- Skip 1 (highest), take 1 (2nd highest)
-- Method 2: Subquery (2nd highest)
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- Method 3: Window function (Nth highest — BEST)
SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = 3; -- Change 3 to any N
-- Nth highest per department
SELECT * FROM (
SELECT name, department, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = 1; -- Highest salary per departmentLIMIT 1 OFFSET 1 skips the highest and returns the 2nd. Subquery finds MAX salary below the overall MAX.
DENSE_RANK handles ties correctly (two people with the same salary get the same rank). PARTITION BY department gives rankings within each department.
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.