ROW_NUMBER, RANK, DENSE_RANK in PostgreSQL - Step-by-Step Execution

Concept Flow - ROW_NUMBER, RANK, DENSE_RANK

Start with ordered data

↓

Apply ROW_NUMBER

↓

Apply RANK

↓

Apply DENSE_RANK

↓

Compare results

↓

End

We start with data sorted by a column, then apply ROW_NUMBER, RANK, and DENSE_RANK functions to assign row positions with different rules for ties.

Execution Sample

PostgreSQL

SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
  RANK() OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM players;

This query assigns row numbers, ranks, and dense ranks to players ordered by their score descending.

Execution Table

Step	Row	Name	Score	ROW_NUMBER	RANK	DENSE_RANK	Explanation
1	1	Alice	100	1	1	1	Highest score, first row
2	2	Bob	90	2	2	2	Second highest score, no tie
3	3	Charlie	90	3	2	2	Tie with Bob, same rank 2, row_number increments
4	4	David	80	4	4	3	Next score after tie, rank skips to 4, dense_rank increments by 1
5	5	Eve	70	5	5	4	Lowest score, ranks increment normally

💡 All rows processed; ranking functions assigned based on score order with tie rules.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5
ROW_NUMBER	0	1	2	3	4	5
RANK	0	1	2	2	4	5
DENSE_RANK	0	1	2	2	3	4

Key Moments - 2 Insights

Why does RANK assign the same number to Bob and Charlie but ROW_NUMBER does not?

RANK gives the same rank to tied scores (Bob and Charlie both 90 get rank 2), but ROW_NUMBER always gives a unique sequential number regardless of ties (Bob is 2, Charlie is 3). See execution_table rows 2 and 3.

Why does RANK skip a number after a tie but DENSE_RANK does not?

RANK skips ranks after ties to reflect gaps (after rank 2 tie, next rank is 4). DENSE_RANK assigns consecutive ranks without gaps (after dense rank 2 tie, next is 3). See execution_table rows 3 and 4.

Visual Quiz - 3 Questions

Look at the execution_table, what is the ROW_NUMBER for Charlie at step 3?

A 2

B 3

C 1

D 4

💡 Hint

Check the ROW_NUMBER column in execution_table row 3.

At which step does the RANK skip a number after a tie?

A Step 4 to 5

B Step 2 to 3

C Step 3 to 4

D Step 1 to 2

💡 Hint

Look at RANK values in execution_table rows 3 and 4.

If David's score changed to 90, what would happen to DENSE_RANK at step 4?

A It would become 2

B It would remain 3

C It would become 4

D It would become 1

💡 Hint

DENSE_RANK assigns same rank to ties; see how Bob and Charlie share dense_rank 2.

Concept Snapshot

ROW_NUMBER assigns unique sequential numbers to rows.
RANK assigns ranks with gaps after ties.
DENSE_RANK assigns ranks without gaps for ties.
All require ORDER BY inside OVER() clause.
Used to rank rows based on ordering criteria.

Full Transcript

This visual execution shows how PostgreSQL ranking functions ROW_NUMBER, RANK, and DENSE_RANK work on a list of players ordered by score descending. ROW_NUMBER gives each row a unique number regardless of ties. RANK assigns the same rank to tied scores but skips numbers after ties. DENSE_RANK assigns the same rank to ties but does not skip numbers. The execution table traces each row's assigned values step-by-step. Variable tracking shows how these values change as rows are processed. Key moments clarify common confusions about ties and numbering. The quiz tests understanding of these differences using the execution visuals.