ROW_NUMBER vs RANK vs DENSE_RANK Explained

1. Why this trio trips people up

What this section should cover

✍️ Draft note — replace this box with your own prose, then delete it

What's the actual moment — an interview question, a production bug, a dashboard that looked wrong — where you first had to pick between these three?
In one sentence, before any syntax: what's a window function, for someone who's never heard the term?
What's the most common wrong assumption you've seen — that these are aggregate functions, or that ORDER BY inside OVER() controls the final row order?

2. The mental model: PARTITION BY and ORDER BY

Quick primer on OVER() before touching the three functions individually — keep this short

✍️ Draft note — replace this box with your own prose, then delete it

What's the simplest analogy you use to explain PARTITION BY to someone who already understands GROUP BY?
Is there a specific moment or phrasing that made window functions "click" for you the first time?

3. The dataset

Introduce the table and why the ties are deliberate — Engineering has a 2-way tie, Sales a 3-way tie, Marketing two separate 2-way ties

Verified by actually running this against SQLite — not hand-computed.

schema.sql

CREATE TABLE employees (
  emp_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department TEXT NOT NULL,
  salary INTEGER NOT NULL
);

INSERT INTO employees (emp_id, name, department, salary) VALUES
  (1,  'Alice Chen',      'Engineering', 95000),
  (2,  'Bob Martinez',    'Engineering', 90000),
  (3,  'Carol Nguyen',    'Engineering', 90000),
  (4,  'Dave Okafor',     'Engineering', 88000),
  (5,  'Eve Patel',       'Engineering', 85000),
  (6,  'Frank Lee',       'Sales',       82000),
  (7,  'Grace Kim',       'Sales',       82000),
  (8,  'Heidi Wagner',    'Sales',       82000),
  (9,  'Ivan Petrov',     'Sales',       79000),
  (10, 'Judy Alvarez',    'Marketing',   91000),
  (11, 'Mallory Singh',   'Marketing',   91000),
  (12, 'Niaj Rahman',     'Marketing',   87000),
  (13, 'Olivia Brooks',   'Marketing',   84000),
  (14, 'Peggy Torres',    'Marketing',   84000),
  (15, 'Sybil Costa',     'Marketing',   80000);

✍️ Draft note — replace this box with your own prose, then delete it

Is this the kind of dataset shape you actually see ties in at work — salary bands, event timestamps, test scores? Give a real example.
Why department + salary instead of, say, timestamps or scores — any reason to keep or swap it?

4. ROW_NUMBER() — always unique, no exceptions

Definition, the query, the verified output, and the one thing to remember: ties get an arbitrary but unique number

row_number.sql

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

department	name	salary	row_num
Engineering	Alice Chen	95000	1
Engineering	Bob Martinez	90000	2
Engineering	Carol Nguyen	90000	3
Engineering	Dave Okafor	88000	4
Engineering	Eve Patel	85000	5
Marketing	Judy Alvarez	91000	1
Marketing	Mallory Singh	91000	2
Marketing	Niaj Rahman	87000	3
Marketing	Olivia Brooks	84000	4
Marketing	Peggy Torres	84000	5
Marketing	Sybil Costa	80000	6
Sales	Frank Lee	82000	1
Sales	Grace Kim	82000	2
Sales	Heidi Wagner	82000	3
Sales	Ivan Petrov	79000	4

Notice Bob and Carol — tied at 90000 — still get different numbers (2 and 3). ROW_NUMBER() doesn't know or care about the tie.

✍️ Draft note — replace this box with your own prose, then delete it

What's a real bug you've seen (or caused) from assuming ROW_NUMBER()'s tie-breaking order was stable when it wasn't?
What's your go-to real use case for ROW_NUMBER — deduplication, pagination, "latest record per key"?

5. RANK() — ties share a rank, then it skips

Definition, the query, the verified output, and the gap behavior right after a tie

rank.sql

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

department	name	salary	rnk
Engineering	Alice Chen	95000	1
Engineering	Bob Martinez	90000	2
Engineering	Carol Nguyen	90000	2
Engineering	Dave Okafor	88000	4
Engineering	Eve Patel	85000	5
Marketing	Judy Alvarez	91000	1
Marketing	Mallory Singh	91000	1
Marketing	Niaj Rahman	87000	3
Marketing	Olivia Brooks	84000	4
Marketing	Peggy Torres	84000	4
Marketing	Sybil Costa	80000	6
Sales	Frank Lee	82000	1
Sales	Grace Kim	82000	1
Sales	Heidi Wagner	82000	1
Sales	Ivan Petrov	79000	4

In Sales, three people tie for rank 1 — and the next rank is 4, not 2. RANK() leaves a gap the size of the tie group.

✍️ Draft note — replace this box with your own prose, then delete it

Where has that gap actually mattered in a real report or dashboard — did a stakeholder ever ask "why did we skip from rank 1 to rank 4"?
Any story of RANK() being used where DENSE_RANK() was what the business actually wanted, or the reverse?

6. DENSE_RANK() — ties share a rank, nothing skips

Definition, the query, the verified output, and how it differs from RANK by never skipping

dense_rank.sql

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

department	name	salary	dense_rnk
Engineering	Alice Chen	95000	1
Engineering	Bob Martinez	90000	2
Engineering	Carol Nguyen	90000	2
Engineering	Dave Okafor	88000	3
Engineering	Eve Patel	85000	4
Marketing	Judy Alvarez	91000	1
Marketing	Mallory Singh	91000	1
Marketing	Niaj Rahman	87000	2
Marketing	Olivia Brooks	84000	3
Marketing	Peggy Torres	84000	3
Marketing	Sybil Costa	80000	4
Sales	Frank Lee	82000	1
Sales	Grace Kim	82000	1
Sales	Heidi Wagner	82000	1
Sales	Ivan Petrov	79000	2

Same three-way tie in Sales — but the next rank is 2, not 4. No gaps, ever.

✍️ Draft note — replace this box with your own prose, then delete it

When have you specifically reached for DENSE_RANK — tiered pricing, medal/leaderboard-style ranking, "top N distinct values" queries?
Has DENSE_RANK() ever silently returned more rows than a "top N" query intended, because of a tie you didn't expect?

7. All three, side by side

The combined query — this is the table that makes the difference click

combined.sql

SELECT department, name, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
       RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees
ORDER BY department, salary DESC;

department	name	salary	row_num	rnk	dense_rnk
Engineering	Alice Chen	95000	1	1	1
Engineering	Bob Martinez	90000	2	2	2
Engineering	Carol Nguyen	90000	3	2	2
Engineering	Dave Okafor	88000	4	4	3
Engineering	Eve Patel	85000	5	5	4
Marketing	Judy Alvarez	91000	1	1	1
Marketing	Mallory Singh	91000	2	1	1
Marketing	Niaj Rahman	87000	3	3	2
Marketing	Olivia Brooks	84000	4	4	3
Marketing	Peggy Torres	84000	5	4	3
Marketing	Sybil Costa	80000	6	6	4
Sales	Frank Lee	82000	1	1	1
Sales	Grace Kim	82000	2	1	1
Sales	Heidi Wagner	82000	3	1	1
Sales	Ivan Petrov	79000	4	4	2

Every row where the three columns diverge is a tie. Watch Marketing: two separate tie groups (91000 and 84000) — DENSE_RANK compresses both gaps, RANK leaves both.

✍️ Draft note — replace this box with your own prose, then delete it

What's the fastest way you'd explain this table to a junior engineer in 30 seconds, pointing at the screen?
Do you have a personal mnemonic for which one skips and which one doesn't?

8. Which one should you actually use?

Practical decision guide — dedup vs. human-facing ranking vs. tiering

✍️ Draft note — replace this box with your own prose, then delete it

Give three real query patterns you've written recently, one per function — what was each one for?
What's your rule of thumb — e.g. "deduping/pagination → ROW_NUMBER, competition-style ranking → RANK, tiering/grouping → DENSE_RANK"? Do you actually follow that, or is it messier in practice?

9. What actually breaks in production

The most valuable section — specific incidents, not generic advice

✍️ Draft note — replace this box with your own prose, then delete it

Describe a specific production incident or wrong number in a dashboard caused by picking the wrong one of these three.
What's the most common code review comment you leave when you see one of these misused?
Have you seen ROW_NUMBER() return a different "row 1" on different runs because ORDER BY wasn't fully deterministic (no tiebreaker column)? What happened downstream?
Any story about performance — one of these over a huge partition with no supporting index, and what it did to the query plan?

10. Cheat sheet

One compact table for people skimming — keep the prose minimal here

Function	Ties	After a tie	Typical use
ROW_NUMBER()	Always unique	Never skips (nothing to skip)	Dedup, pagination, "latest per key"
RANK()	Same rank	Skips by tie-group size	Competition-style ranking
DENSE_RANK()	Same rank	Never skips	Tiering, "top N distinct values"

✍️ Draft note — replace this box with your own prose, then delete it

Is there a phrasing or mnemonic you'd want future-you to see when skimming this in six months?

11. Try it yourself

Close the loop — point to hands-on practice, one clear takeaway

✍️ Draft note — replace this box with your own prose, then delete it

What's the single thing you want a reader to remember above everything else in this article?

Practice window functions live on TableNotFound →