RANK(), DENSE_RANK(), and ROW_NUMBER() all number rows within an ordered result set, and they all look nearly identical in simple cases. The difference only shows up once two or more rows tie on the value you're ordering by — and that's exactly the situation that trips people up in interviews and in production reports.

All three are window functions, used with an OVER (ORDER BY ...) clause, optionally combined with PARTITION BY to restart the numbering within each group.

Side-by-Side on Tied Values

Here's the clearest way to see the difference — ranking employees by salary where two of them earn exactly the same amount:

SELECT
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK()       OVER (ORDER BY salary DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM employees;

Result:

name | salary | row_num | rank_num | dense_rank_num
--------|--------|---------|----------|---------------
Alice | 82000 | 1 | 1 | 1
Bob | 75000 | 2 | 2 | 2
Priya | 75000 | 3 | 2 | 2
Diego | 61000 | 4 | 4 | 3

Bob and Priya are tied at 75000, and that's where all three functions disagree:

  • ROW_NUMBER() never ties — it just hands out 1, 2, 3, 4 in order, arbitrarily breaking the tie between Bob and Priya.
  • RANK() gives both tied rows the same rank (2), but then skips the next number — Diego jumps straight to 4, because two rows already claimed ranks 2 and "would-be 3".
  • DENSE_RANK() also gives both tied rows the same rank (2), but does not skip — Diego gets 3, the very next number after 2.

Which One Should You Use?

Pick based on what the tie should mean in your result:

  • Use ROW_NUMBER() when you just need a unique sequence number per row — for example, picking "the first row per group" with a filter like WHERE row_num = 1, where ties don't matter.
  • Use RANK() for competition-style ranking where a gap after a tie makes sense (like Olympic medal placement: two golds means no silver, straight to bronze).
  • Use DENSE_RANK() when you want consecutive rank numbers with no gaps, even after ties — often the more intuitive choice for things like "salary tier" or "top 3 distinct scores".

Ranking Within Groups

Add PARTITION BY to restart the ranking separately for each department:

SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Result:

name | department | salary | dept_rank
--------|-------------|--------|----------
Alice | Engineering | 82000 | 1
Bob | Engineering | 75000 | 2
Priya | Sales | 75000 | 1
Diego | Sales | 61000 | 2

Each department now has its own independent rank 1 — the partition resets the counting at each new department, which is the same pattern used when grouping rows with GROUP BY, just without collapsing the individual rows away.