RANK and DENSE_RANK difference in SQL - Step-by-Step Execution

Concept Flow - RANK and DENSE_RANK difference

Start with ordered data

↓

Apply RANK function

↓

Assign ranks with gaps for ties

↓

Apply DENSE_RANK function

↓

Assign ranks without gaps for ties

↓

Compare results

↓

End

We start with ordered data, apply RANK which assigns ranks with gaps for ties, then apply DENSE_RANK which assigns ranks without gaps, and finally compare the two results.

Execution Sample

SQL

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

This query ranks players by score using both RANK and DENSE_RANK to show the difference in handling ties.

Execution Table

Row	Name	Score	RANK() Result	DENSE_RANK() Result	Explanation
1	Alice	100	1	1	Highest score, rank 1 for both
2	Bob	90	2	2	Second highest, no tie yet
3	Charlie	90	2	2	Tie with Bob, same rank
4	David	80	4	3	After tie, RANK skips rank 3, DENSE_RANK continues sequentially
5	Eve	70	5	4	No tie, ranks continue
6	Frank	70	5	4	Tie with Eve, same rank
7	Grace	60	7	5	After tie, RANK skips rank 6, DENSE_RANK continues sequentially

💡 All rows processed, showing how RANK leaves gaps after ties, DENSE_RANK does not.

Variable Tracker

Variable	Row 1	Row 2	Row 3	Row 4	Row 5	Row 6	Row 7
RANK()	1	2	2	4	5	5	7
DENSE_RANK()	1	2	2	3	4	4	5

Key Moments - 2 Insights

Why does RANK() skip numbers after ties but DENSE_RANK() does not?

RANK() assigns the same rank to tied rows but leaves gaps in the sequence for the next rank, as seen in rows 3 and 4 in the execution_table. DENSE_RANK() assigns ranks without gaps, continuing the sequence immediately after ties.

What happens to the rank numbers when multiple rows have the same score?

Both RANK() and DENSE_RANK() assign the same rank number to all tied rows, as shown for Bob and Charlie with score 90. The difference is in how the next rank number is assigned after the tie.

Visual Quiz - 3 Questions

Look at the execution_table, what is the RANK() value for David (Row 4)?

A 3

B 2

C 4

D 5

💡 Hint

Check the RANK() Result column for Row 4 in the execution_table.

At which row does DENSE_RANK() assign rank 3?

A Row 3 (Charlie)

B Row 4 (David)

C Row 5 (Eve)

D Row 2 (Bob)

💡 Hint

Look at the DENSE_RANK() Result column in the execution_table.

If Eve's score changed to 85, how would RANK() for David change?

A It would become 5

B It would remain 4

C It would become 3

D It would become 2

💡 Hint

Consider how ranks shift when a new score is inserted between existing scores, referencing the variable_tracker.

Concept Snapshot

RANK() assigns ranks with gaps after ties.
DENSE_RANK() assigns ranks without gaps.
Both assign same rank to tied rows.
Use ORDER BY to define ranking order.
RANK() skips numbers after ties; DENSE_RANK() does not.

Full Transcript

This visual execution shows how RANK() and DENSE_RANK() assign ranks to rows ordered by score. Both functions assign the same rank to tied rows, but RANK() leaves gaps in the ranking sequence after ties, while DENSE_RANK() continues ranks sequentially without gaps. For example, Bob and Charlie tie with score 90 and both get rank 2. The next rank for David is 4 with RANK() but 3 with DENSE_RANK(). This difference is important when you want continuous ranking numbers or want to reflect gaps caused by ties.