Q17: How do you find the Nth highest salary in a table?
There are two common ways to solve this classic interview question: using DENSE_RANK() or using an offset subquery.
Robust Nth Highest Salary Query
-- Method 1: Using Window Functions (Handles tied salaries correctly)
WITH RankedSalaries AS (
SELECT employee_id, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
FROM employees
)
SELECT DISTINCT salary
FROM RankedSalaries
WHERE rnk = 2; -- Change N here
-- Method 2: Generic OFFSET Subquery (MySQL / Postgres)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1; -- OFFSET N-1💡 DENSE_RANK correctly handles duplicate salaries without skipping rank numbers.
Key Takeaways
DENSE_RANK()handles duplicate salary values safely without missing ranks.- Always include
DISTINCTor group partitions when selecting Nth values.