ROW_NUMBER vs RANK vs DENSE_RANK

The one-sentence difference

All three functions number rows within a window, ordered by whatever you put in ORDER BY — and when there are no ties, they produce identical results. They only disagree the moment two rows tie: ROW_NUMBER pretends the tie doesn't exist, RANK treats tied rows as sharing a spot and leaves a gap afterward, and DENSE_RANK treats tied rows as sharing a spot with no gap.

If you've read Window Functions Explained Like You're 10, this picks up right where the class_rank example left off — except this time, two kids get the exact same score.

A leaderboard with a tie

Here's the result of a math quiz. Notice Mia and Leo both scored 95, and Sam and Zoe both scored 82:

Two ties: Mia/Leo at 95, and Sam/Zoe at 82.

ROW_NUMBER() — always unique

ROW_NUMBER() hands out 1, 2, 3, 4, 5, 6 no matter what — it doesn't care that Mia and Leo tied. When values are equal, the database picks an order between them (often insertion order, but it's not guaranteed unless you add a tiebreaker column to ORDER BY):

SELECT name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS rn
FROM math_quiz;

RANK() — ties share, then skip

RANK() gives Mia and Leo both rank 1 — but then Ana, who's genuinely in 3rd place, gets rank 3, not 2. Rank 2 is skipped because two people already occupy rank 1:

SELECT name, score,
       RANK() OVER (ORDER BY score DESC) AS rnk
FROM math_quiz;

DENSE_RANK() — ties share, no skip

DENSE_RANK() also gives Mia and Leo rank 1, but Ana gets rank 2 — no gap. Every distinct score gets the next consecutive number:

SELECT name, score,
       DENSE_RANK() OVER (ORDER BY score DESC) AS drnk
FROM math_quiz;

DENSE_RANK answers a slightly different question than RANK: not "what position is this row in," but "how many distinct score levels are at or above this one."

Side-by-side comparison

Which one should you use?

• Need exactly one row per group, no ties possible? Use ROW_NUMBER — deduplication, pagination, "pick the latest one."
• Ranking a competition or leaderboard where ties should share a spot, and gaps make sense? Use RANK.
• Ranking into consecutive tiers or levels, where gaps would be confusing? Use DENSE_RANK — salary bands, "top 3 distinct prices," grading tiers.

Real use cases

The most common real-world use of ROW_NUMBER is deduplication — keeping only one row per group, like the most recent order per customer:

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC
         ) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1;  -- one row per customer

And "top 3 distinct scores per class" is a natural fit for DENSE_RANK, since it correctly allows more than 3 rows if there's a tie for 3rd:

WITH ranked AS (
  SELECT name, class, score,
         DENSE_RANK() OVER (
           PARTITION BY class
           ORDER BY score DESC
         ) AS drnk
  FROM spelling_test
)
SELECT * FROM ranked WHERE drnk <= 3;

Common mistakes

• Using ROW_NUMBER for "top N" when ties should count. If two rows tie for 3rd place, ROW_NUMBER arbitrarily keeps one and drops the other — RANK or DENSE_RANK let both through.
• Assuming RANK and DENSE_RANK are interchangeable. They only match when there are zero ties. Once a tie appears, every rank after it diverges.
• Forgetting ORDER BY inside OVER(). All three functions need it to know what "first" means — without it, most databases will error out.
• Filtering the rank in WHERE. Like any window function, you can't write WHERE rn = 1 directly — wrap it in a subquery or CTE first. (See SQL execution order explained.)