Nth Highest Salary

Hook: This question is a favorite because one tiny choice—ROW_NUMBER vs DENSE_RANK—can change the answer when salaries tie.

Question: How do I find the Nth highest salary in SQL?

Answer: The usual interview-safe answer is to use a window function and rank salaries in descending order. If duplicate salaries should count as one rank, use DENSE_RANK(); if every row should count separately, use ROW_NUMBER(). Then filter to the row where the rank equals N.

Interview-Ready Answer: I would rank the salaries in descending order with a window function, then filter for rank N. For the common interview version, where duplicate salaries should share the same place, I use DENSE_RANK() so 100, 100, 90 gives ranks 1, 1, 2 instead of skipping numbers. That makes the query correct even when there are ties, and if there are fewer than N distinct salaries, I can return no row or NULL depending on the requirement.

What the question is really asking

The phrase Nth highest salary sounds simple, but interviewers are checking whether you understand ties and ranking. In most interview settings, highest salary means the distinct salary value at position N, not the Nth employee row. That is why the best tool is usually a window function, which means a function that looks across a set of rows while still returning one result per row.

How it works under the hood

1. Start with the salary values you want to rank, usually excluding NULL because a missing salary should not compete with real values.
2. Sort the rows from highest to lowest salary.
3. Assign a rank to each row. If two rows have the same salary, they are peers, meaning rows tied on the sort key.
4. Use DENSE_RANK() when peers should share the same rank with no gaps. Example: 100, 100, 90 becomes ranks 1, 1, 2.
5. Filter for the row where the rank equals N. If no such rank exists, the result is empty or NULL, depending on how you write the query.

Why DENSE_RANK() is usually the right answer

For this problem, DENSE_RANK() is the cleanest fit because it matches human expectations: equal salaries get equal place, and the next salary gets the next number with no skipped ranks. That is exactly what most interviewers want unless they explicitly say duplicates should count separately.

Performance and edge cases

• Time complexity: usually O(n log n) because the database must sort salaries for the window order. The ranking itself is cheap after sorting.
• Space complexity: depends on the engine, but the sort can use extra memory and may spill to disk if the working memory is too small. On a million-row table, that spill can turn a fast query into a noticeably slower one.
• Indexes: an index on salary can help the optimizer, especially for simple top-N queries, but the engine may still need a full ordered pass for ranking.
• NULLs: filter them out or handle them explicitly. Different databases can sort NULL differently, so do not leave that to chance.
• Missing N: if there are only 3 distinct salaries and you ask for the 5th, return no row or NULL. Interviewers often like you to mention this.

Memory model: think of the database as lining up salary cards from tallest to shortest. DENSE_RANK() puts the same-height cards on the same step and never leaves empty steps. That is why it is the safest mental model for this question.

Real-world story

Imagine a payroll analytics service inside a large HR platform. Finance wants a report showing the 5th highest salary across the company so they can check compensation bands and outliers. The engineer writes a query with ROW_NUMBER() because it looks simple, but the report is wrong when two executives share the same salary. One tied salary consumes an extra row, so the dashboard shows the wrong 5th place and hides the real salary band.

What goes wrong: HR opens the dashboard on Monday morning and sees a mismatch between the report and the source system. Logs show the query returned a row, but the values do not match the expected rank. Users complain that the top-compensation list is inconsistent across departments, and the fix is to switch to DENSE_RANK() and exclude NULL salaries. The bug is subtle because the query does return data; it is just the wrong data.

Follow-up & Tricky Questions:

• How do you return the employee names too? Rank the salaries in a CTE, then join the ranked result back to the employee table. If multiple employees share the Nth salary, you will correctly get all of them.
• What if duplicate salaries should count separately? Use ROW_NUMBER() instead of DENSE_RANK(). That treats each row as a unique position, which is a different business rule.
• What if there is no Nth salary? Return an empty result or NULL by using a left join or a scalar wrapper, depending on how the interviewer wants the output shaped.
• Can you solve it without window functions? Yes, with a correlated subquery that counts how many distinct salaries are greater than the current one, but it is usually harder to read and often slower on large tables.
• How should NULL salaries be handled? Usually exclude them with WHERE salary IS NOT NULL. That keeps missing data from affecting the ranking.
• Is RANK() the same as DENSE_RANK()? No. RANK() leaves gaps after ties, while DENSE_RANK() does not. For the Nth distinct salary, DENSE_RANK() is usually the right choice.
• Why not just use LIMIT 1 OFFSET N-1? That answers the Nth row after sorting, not the Nth distinct salary. With duplicates, it can give a different and often wrong answer.
• What if two people share the Nth salary? That is fine if the output is a salary value. If the output must include people, join back to the table and return every matching employee.

Common Mistakes:

• Using ROW_NUMBER() when the question really asks for the distinct Nth salary. Fix: use DENSE_RANK().
• Forgetting duplicate salaries exist. Fix: always ask whether ties should share a rank.
• Not handling NULL salaries. Fix: filter them out unless the business rule says otherwise.
• Assuming the query must always return one row. Fix: if N is too large, the correct result may be empty or NULL.

Memory Hook: Picture a podium with tied runners: DENSE_RANK() gives the same medal to ties and never leaves an empty place number. ROW_NUMBER() is the strict attendance list; DENSE_RANK() is the real ranking board.

Cheat Sheet:

• Use DENSE_RANK() for the Nth distinct salary.
• Use ROW_NUMBER() only when every row must be unique in order.
• Exclude NULL salaries unless told otherwise.
• Filter on rank = N after ordering salaries descending.
• Expect O(n log n) sort cost on large tables.
• If N is too large, return no row or NULL.

Practice Tasks:

• Write the query for the 2nd highest distinct salary.
• Modify the query so it returns all employees whose salary matches the Nth salary.
• Rewrite the solution with ROW_NUMBER() and observe how the answer changes when there are duplicates.