Visual walkthrough — Window functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD

This page assumes nothing. If you have not met the OVER clause yet, read the parent topic first — but we re-explain every symbol here anyway.

---

Step 1 — What is a row, really? (start from a spreadsheet)

WHAT. Imagine a tiny table called employees. It has four people. Each person is a row: a horizontal strip of facts. We care about one column, salary.

WHY. Before we can rank rows we must agree what a row is and that the rows are, at first, in no particular order — a database does not guarantee order until we ask for one.

PICTURE. Four unordered cards, each holding a salary. Notice there are two cards showing 900 — those two are a tie, and ties are the whole reason three different rankers exist.

---

Step 2 — ORDER BY: pouring the rows into a line

WHAT. We choose an order: ORDER BY salary DESC. DESC means descending — biggest first. The rows now line up: 900, 900, 700, 500.

WHY. Ranking is a question about position in a line. "Who is first?" has no answer until a line exists. This is exactly why every ranking function requires an ORDER BY inside its OVER (...) — without it the numbers are non-deterministic (the parent note's first Common Mistake).

PICTURE. The scattered cards from Step 1 slide into a single sorted column. The arrow shows the direction of "better" (higher salary = closer to the front).

We will reuse $\prec$ in every remaining step, so pin it down now.

---

Step 3 — ROW_NUMBER: just count heads

WHAT. Walk down the line and hand out 1, 2, 3, 4. That is ROW_NUMBER().

WHY. It answers the simplest question: "What is your position in the line?" It does not look at values — only at position. So even though the two 900s are equal, one still gets 1 and the other 2. The tie is broken arbitrarily (the database picks).

PICTURE. A counter clicking 1 → 2 → 3 → 4 down the sorted column. The two equal 900s get different numbers — highlighted to show ROW_NUMBER ignores equality.

$\text{ROW\_NUMBER}(r)=(\text{how many rows are at or before }r\text{ in the line})$

• $r$ :: the current row
• the right-hand side :: literally the click-count of the counter as it reaches $r$

---

Step 4 — RANK: reward equality, then pay the debt

WHAT. Now we respect ties. Both 900s should share rank 1. The 700 then gets rank 3 — not 2. That "jump" is the famous skip.

WHY does it skip? Because RANK asks a different question than ROW_NUMBER: not "your position" but "how many rows are strictly ahead of you?" Add 1 to that.

PICTURE. For the 700 card, we draw a bracket around every row strictly ahead of it — that is both 900s, so 2 rows. Rank $=1+2=3$. The gap at "2" appears automatically; nobody deleted a number.

$\text{RANK}(r)=1+|\{x:x\prec r\}|$

• $\{x:x\prec r\}$ :: the set of rows sitting strictly ahead of $r$
• $|\cdots |$ :: the size of that set — how many rows are in it
• $1+$ :: because you yourself occupy the next spot after everyone ahead

Apply it to all four rows:

salary	rows strictly ahead	RANK $=1+$count
900	none → 0	1
900	none higher → 0	1
700	{900, 900} → 2	3
500	{900, 900, 700} → 3	4

The skip from 1 to 3 is the two rows ahead of the 700, counted honestly.

---

Step 5 — DENSE_RANK: count values, not rows

WHAT. Same idea, one word changed. Instead of counting rows ahead, count distinct values ahead. The 700 now has only one distinct value ahead of it (namely 900), so its dense rank is $1+1=2$ — no gap.

WHY. DENSE_RANK answers "how many salary levels beat you?" The two 900s are the same level, so they count once, not twice. That single change removes every gap.

PICTURE. The same 700 card, but now the bracket collapses the two 900s into one shaded band labelled "distinct level 900". One band ahead → dense rank 2.

$\text{DENSE\_RANK}(r)=1+|\{\text{distinct }v:v\prec r\}|$

• $v$ :: a value, not a row
• $\{\text{distinct }v:v\prec r\}$ :: the set of unique values sitting ahead — duplicates merged
• everything else :: identical to RANK

salary	distinct values ahead	DENSE_RANK
900	{} → 0	1
900	{} → 0	1
700	{900} → 1	2
500	{900, 700} → 2	3

---

Step 6 — Edge case: no ties at all

WHAT. Feed in 900, 700, 500, 300 — every value unique.

WHY show this? To prove the three functions agree exactly when nothing ties. The differences we saw are entirely a tie phenomenon; remove ties and the columns become identical.

PICTURE. Three columns of numbers side by side — all reading 1, 2, 3, 4. When "rows ahead" always equals "distinct values ahead" (because no duplicates), the formulas coincide.

$\text{No ties}⟹|\{x:x\prec r\}|=|\{\text{distinct }v:v\prec r\}|⟹\text{RANK}=\text{DENSE\_RANK}=\text{ROW\_NUMBER}$

---

Step 7 — Edge case: everyone ties (all equal)

WHAT. Feed in 500, 500, 500, 500 — one giant tie.

WHY. This stress-tests each definition at its extreme:

• ROW_NUMBER still hands out 1, 2, 3, 4 (it never cares about equality — arbitrary order).
• RANK: rows strictly ahead of any 500? Zero for all of them → every rank is 1.
• DENSE_RANK: distinct values ahead? Zero → every dense rank is 1.

PICTURE. Four identical 500 cards. ROW_NUMBER's counter still climbs 1,2,3,4; RANK and DENSE_RANK both flatline at 1,1,1,1. The contrast makes ROW_NUMBER's "I don't look at values" nature vivid.

salary	ROW_NUMBER	RANK	DENSE_RANK
500	1	1	1
500	2	1	1
500	3	1	1
500	4	1	1

---

The one-picture summary

Every ranker is the same walk down the same sorted line — they differ only in what they count as they walk:

• ROW_NUMBER — count your steps. (ties ignored)
• RANK — count rows ahead, add 1. (ties → gaps)
• DENSE_RANK — count distinct values ahead, add 1. (ties → no gaps)

Recall Feynman retelling — the whole walkthrough in plain words

Line up kids by height, tallest at the front. Now three judges walk the line. Judge ROW_NUMBER doesn't care about height — she just taps each head: "one, two, three, four." Twins of the same height get different numbers; she flips a coin. Judge RANK hands out medals. Before giving your medal she asks: "How many kids are strictly taller than you?" Add one — that's your medal. Two twins tallest? Both get gold (0 taller → medal 1). The next kid has two taller kids, so medal 3. Silver simply never gets awarded — that's the gap. Judge DENSE_RANK is kinder: she asks "how many height levels are above you?" The twins are one level, counted once. So the third kid has one level above → bronze… I mean, rank 2. No gap. Feed all this a line with no twins and all three judges shout the same numbers. Feed it a line where everyone is identical and ROW_NUMBER still climbs 1-2-3-4 while the other two both freeze at 1. That single behaviour — rows counted vs. values counted vs. steps counted — is the entire lesson.

---

Connections

• GROUP BY and Aggregate Functions — collapses the line into one blob; here we keep every kid standing.
• Common Table Expressions (CTE) — the clean place to compute these ranks, then filter them.
• SQL Logical Query Processing Order — why ranks aren't available in WHERE.
• Window Frames ROWS vs RANGE — once you can rank, frames let you sum over a moving slice.
• Self Joins — the old, verbose way to answer "who's ahead of me?".