SQL Medium Asked at AmazonAsked at GoogleAsked at MicrosoftAsked at Goldman Sachs

Write a query to find the Nth highest salary in a table, where N is a parameter, handling ties correctly.

For Data Analyst Data Scientist Data Engineer

The short answer

Use DENSE_RANK() to assign rank without gaps — the Nth distinct salary value is the row where DENSE_RANK equals N. RANK() would produce wrong results when ties occur, and a plain ORDER BY LIMIT/OFFSET approach ignores ties entirely.

How to think about it

What the question is really testing

This is a classic ranking question, and the trap is right in the data: what happens when two people share the top salary? The interviewer wants to see whether you reach for DENSE_RANK (correct) or accidentally use RANK or LIMIT/OFFSET (wrong under ties).

Step 1 — understand the three ranking functions

Function	Behavior with ties	Gap after tie?
`ROW_NUMBER()`	Arbitrary tiebreaker, each row gets a unique number	No
`RANK()`	Tied rows share a rank, next rank skips	Yes — rank 1,1,3
`DENSE_RANK()`	Tied rows share a rank, next rank is consecutive	No — rank 1,1,2

For “Nth highest salary,” you need DENSE_RANK — otherwise when two people share rank 1, RANK jumps to 3 and rank 2 never exists.

Step 2 — the general solution

WITH ranked AS (
  SELECT
    employee_id,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM employees
)
SELECT employee_id, salary
FROM ranked
WHERE dr = 2;   -- change 2 to find any Nth highest

Step 3 — run it and experiment

The dataset below has ties at the top (two people on 120,000) and a tie for third. Try changing rnk = 2 to rnk = 3 to see the third-highest salary.

Why LIMIT/OFFSET is wrong

-- WRONG: skips tied salaries, non-deterministic under ties
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

DISTINCT + OFFSET deduplicates first and then skips rows — which can accidentally give the right answer on clean data but fails when the N-1th salary is tied. DENSE_RANK is explicit about intent and handles all edge cases cleanly.

Handling the case where N exceeds distinct salary count

WITH ranked AS (
  SELECT DISTINCT
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM employees
)
SELECT salary FROM ranked WHERE dr = 10;
-- Returns 0 rows if fewer than 10 distinct salary values exist

Simply returning zero rows is usually the right behavior — makes it easy for the caller to detect the “N out of range” case.

Learn it properly Ranking functions