How Do You Find the Nth Highest Salary in SQL?
Find the Nth highest salary in SQL using DENSE_RANK, correlated subqueries and LIMIT/OFFSET, with tie handling, edge cases and interview-ready examples.
Expected Interview Answer
You find the Nth highest salary either with a window function such as DENSE_RANK() to rank salaries and filter for rank = N, or portably with a correlated subquery that counts how many distinct salaries are higher and keeps the row where that count equals N-1.
The cleanest modern approach uses DENSE_RANK() OVER (ORDER BY salary DESC) so ties share a rank and the Nth distinct salary is unambiguous, unlike ROW_NUMBER() which breaks ties arbitrarily. On engines without window functions you can use LIMIT with OFFSET N-1 over DISTINCT salaries, or a correlated subquery. Always decide up front whether duplicate salaries should count once (DENSE_RANK) or produce gaps (RANK), and handle the case where fewer than N salaries exist by returning NULL.
- DENSE_RANK handles tied salaries correctly
- Correlated subquery works on legacy engines without window functions
- LIMIT/OFFSET is concise for simple cases
- Explicitly returns NULL when N exceeds distinct salaries
- Generalises to Nth value per group with PARTITION BY
AI Mentor Explanation
Finding the Nth highest salary is like naming the third-highest run scorer of a season when several batters share the same tally. You rank every batter by runs, let tied scores share a position, and then read off whoever sits at the third distinct level — not the third name in a list where equal scores were split apart arbitrarily.
Step-by-Step Explanation
Step 1
Clarify tie handling
Decide whether duplicate salaries should count as one rank (DENSE_RANK) or create gaps (RANK) before writing the query.
Step 2
Rank the salaries
Apply DENSE_RANK() OVER (ORDER BY salary DESC) in a subquery or CTE so each distinct salary gets a rank.
Step 3
Filter for rank N
Select rows where the computed rank equals N to get the Nth highest distinct salary.
Step 4
Consider a portable fallback
On engines without window functions, use a correlated subquery counting higher distinct salaries, or DISTINCT with LIMIT 1 OFFSET N-1.
Step 5
Handle out-of-range N
Ensure the query returns NULL (not an error) when there are fewer than N distinct salaries.
What Interviewer Expects
- Choice between DENSE_RANK, RANK and ROW_NUMBER with reasoning
- Correct handling of duplicate salaries
- A portable subquery alternative to window functions
- Awareness of the fewer-than-N edge case returning NULL
- Ability to generalise to per-department (PARTITION BY)
Common Mistakes
- Using ROW_NUMBER() and getting wrong results when salaries tie
- Forgetting DISTINCT so duplicate salaries skew the offset
- Off-by-one error using OFFSET N instead of OFFSET N-1
- Not handling the case where N exceeds available salaries
- Assuming LIMIT/OFFSET exists on every database engine
Best Answer (HR Friendly)
“You sort the salaries from highest to lowest and pick out the one in the position you need, being careful when two people earn the same amount. Modern databases have a ranking function that groups equal salaries together so the count stays correct.”
Code Example
-- Nth highest salary (N = 3) using DENSE_RANK
WITH ranked AS (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT DISTINCT salary AS nth_highest_salary
FROM ranked
WHERE rnk = 3;-- Same result without window functions
SELECT MIN(salary) AS nth_highest_salary
FROM employees e1
WHERE 3 - 1 = (
SELECT COUNT(DISTINCT e2.salary)
FROM employees e2
WHERE e2.salary > e1.salary
);Follow-up Questions
- What is the difference between RANK, DENSE_RANK and ROW_NUMBER?
- How would you find the Nth highest salary per department?
- How do you return NULL when fewer than N salaries exist?
- Why can LIMIT/OFFSET give wrong results without DISTINCT?
- How does the correlated subquery approach scale on large tables?
MCQ Practice
1. Which function correctly ranks tied salaries with no gaps so the Nth distinct value is clear?
DENSE_RANK() gives tied rows the same rank without leaving gaps, so rank = N maps to the Nth distinct salary.
2. To get the 4th highest distinct salary with LIMIT/OFFSET, which clause is correct?
OFFSET skips N-1 rows; for the 4th value over DISTINCT salaries you skip 3 and take 1.
3. Why might ROW_NUMBER() return an incorrect Nth highest salary?
ROW_NUMBER() never repeats a number, so equal salaries get different positions chosen arbitrarily, breaking the ranking.
Flash Cards
Which window function handles ties best for Nth highest? — DENSE_RANK() — tied salaries share a rank with no gaps, so rank = N is the Nth distinct salary.
Portable way without window functions? — Correlated subquery counting DISTINCT salaries greater than the current row, matched to N-1.
LIMIT/OFFSET for the Nth value? — SELECT DISTINCT salary ORDER BY salary DESC LIMIT 1 OFFSET N-1.
ROW_NUMBER vs DENSE_RANK for this problem? — ROW_NUMBER breaks ties arbitrarily; DENSE_RANK keeps tied salaries on the same rank.
What if N exceeds the number of salaries? — The query should return NULL rather than error — MIN over an empty set or filtered rank yields NULL.