BEGINNERWindow functions

What is the difference between RANK, DENSE_RANK and ROW_NUMBER?

Short answer

They differ only in how they handle ties: ROW_NUMBER never ties, RANK ties and skips, DENSE_RANK ties and does not skip.

Full answer

All three assign a number within an ordered window. They differ only in tie handling.

For salaries 100, 100, 90, 80:

Function	Result
`ROW_NUMBER()`	1, 2, 3, 4
`RANK()`	1, 1, 3, 4
`DENSE_RANK()`	1, 1, 2, 3

ROW_NUMBER assigns distinct numbers even to tied rows. Which tied row gets which number is arbitrary unless you add a tiebreaker to the ORDER BY.

RANK gives ties the same number and then skips — after two rows at rank 1, the next is rank 3.

DENSE_RANK gives ties the same number and does not skip.

Example

sql
-- Deduplicate: keep the most recent row per customer
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY updated_at DESC, id DESC   -- id breaks ties deterministically
    ) AS rn
    FROM customer_snapshots
) t WHERE rn = 1

What the interviewer is assessing

The choice follows from what the question is asking about.

Use ROW_NUMBER for deduplication — "keep one row per customer" — and always add a deterministic tiebreaker, or you get a different row on each run.

Use RANK for competition-style ranking where two firsts mean no second.

Use DENSE_RANK when you are ranking distinct values — "the second highest salary" — because a skipped rank would make the answer disappear.

If you get stuck

Hint 1. Think about what happens with tied values.
Hint 2. Two of them tie; only one of those skips.

Practise this

SQL

Second highest salary per department

Return the second highest salary in each department. If a department has fewer than two distinct salaries, it should not appear. Handle ties…

Intermediate8 min