Find the Nth Highest Salary

Why the plain LIMIT trick breaks

The first query most people write is:

SELECT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 5;

It skips five rows and returns the sixth. That is only right when every salary is different. In this table two people earn 140000 and three earn 135000, so skipping five rows lands on the second 140000 and returns the 5th highest amount, not the 6th. Interviewers pick data like this on purpose.

Three ways to get it right

1. DISTINCT with LIMIT and OFFSET. Remove the duplicates first, then skip N - 1 amounts:

SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 5;

This returns the amount only. It is short and easy to explain, which makes it a good first answer.

2. DENSE_RANK. Rank the amounts so ties share a rank, then filter:

WITH ranked AS (
  SELECT first_name, last_name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
  FROM employees
)
SELECT first_name, last_name, salary
FROM ranked
WHERE salary_rank = 6;

This is the answer most interviewers want to see, because it also returns the people and handles ties without extra work. Swap ORDER BY salary DESC for ORDER BY salary to get the Nth lowest instead.

3. A correlated subquery. Keep the salary that has exactly N - 1 distinct amounts above it:

SELECT DISTINCT salary FROM employees e1
WHERE 5 = (SELECT COUNT(DISTINCT salary)
           FROM employees e2
           WHERE e2.salary > e1.salary);

It works in databases without window functions, but it compares every row with every other row, so mention that it gets slow on big tables.

RANK, DENSE_RANK or ROW_NUMBER?

• DENSE_RANK gives ties the same rank and leaves no gaps (1, 2, 2, 3). Rank N is always the Nth distinct amount, which is what this question asks for.
• RANK gives ties the same rank but then skips numbers (1, 2, 2, 4). Rank 3 may not exist at all.
• ROW_NUMBER gives every row its own number, so it behaves like the broken LIMIT query above.

What to say in the interview

Ask two questions before writing anything: does "Nth highest" mean distinct amounts, and what should come back when fewer than N distinct salaries exist? Then write the DENSE_RANK version, mention the LIMIT and OFFSET shortcut, and explain why ROW_NUMBER would be wrong. For the per-department version of this question, add PARTITION BY department_id inside the OVER clause.