MiddleCodeOccasionalNot answered yet

Find the N-th highest distinct salary, handling ties

Table Employee(id, name, salary). Return the N-th highest distinct salary.

Requirements:

"highest distinct" — if several employees share a salary, that salary counts once
N = 2 must yield the second distinct salary even when many people share the top one
a pseudo-column rank cannot be filtered in the same SELECT's WHERE — it must be wrapped in a subquery

SELECT salary
FROM   Employee
-- your query here

Write the query.

Rank rows with DENSE_RANK() OVER (ORDER BY salary DESC) in a subquery, then filter WHERE rnk = N. DENSE_RANK handles ties — shared salaries share a rank and the next rank is not skipped, so N=2 is the second distinct salary. Use ROW_NUMBER for exactly one row, RANK to skip ranks on ties.

✗Using ROW_NUMBER when ties should share a rank (use DENSE_RANK)
✗Assuming LIMIT OFFSET N deduplicates tied salaries
✗Confusing RANK (skips ranks) with DENSE_RANK (no gaps)

→How do RANK, DENSE_RANK, and ROW_NUMBER differ on three people sharing the top salary?
→Why must the ranking go in a subquery rather than the WHERE clause directly?

Contents

Task

Find the N-th highest salary, correctly handling ties.

SELECT salary
FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM   Employee
) ranked
WHERE rnk = :N;

How it works

The window function DENSE_RANK() assigns a rank to each row by descending salary. The subquery is needed because the rnk pseudo-column cannot be used directly in the WHERE of the same SELECT — it is computed later.

Choosing the ranking function for ties:

DENSE_RANK() — equal salaries share a rank and the next rank is not skipped (1,1,2). N=2 is the second distinct salary.
RANK() — equals share a rank, but the next is skipped (1,1,3).
ROW_NUMBER() — a unique number per row (1,2,3), ignoring ties.

⚠️ For "the N-th distinct salary" you specifically want DENSE_RANK; ROW_NUMBER gives the wrong answer on duplicates.

Open topic in roadmap →