SQL Interview Question: Find the Nth Highest Salary

A favourite interview question: given employees(id, name, department, salary), find the Nth highest salary. Mention how you handle ties and missing values.

1. DISTINCT with LIMIT/OFFSET

-- 3rd highest distinct salary
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;     -- N - 1

Returns no row if there are fewer than N distinct salaries. Wrap it to return NULL instead: SELECT (SELECT ... ) AS third_highest;

2. DENSE_RANK (the most flexible)

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

DENSE_RANK gives tied salaries the same rank with no gaps, which is what "Nth highest salary" usually means. RANK would leave gaps after ties; ROW_NUMBER would ignore ties.

3. Correlated subquery (works without window functions)

SELECT DISTINCT e1.salary
FROM employees e1
WHERE 2 = (                        -- N - 1 salaries are higher
  SELECT COUNT(DISTINCT e2.salary)
  FROM employees e2
  WHERE e2.salary > e1.salary
);

Correct but slow on big tables; good to show you understand the logic.

Follow-up: highest salary in each department

SELECT department, name, salary
FROM (
  SELECT e.*, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees e
) ranked
WHERE rnk = 1;       -- includes everyone tied for the top

Talking through ties, NULL salaries and the empty-result case shows interviewers you think about edge cases.