RANK and DENSE_RANK difference in SQL - Deep Dive

Overview - RANK and DENSE_RANK difference

What is it?

RANK and DENSE_RANK are SQL functions used to assign a ranking number to rows within a result set based on the order of a specified column. Both functions order rows and assign ranks, but they handle ties differently. RANK leaves gaps in the ranking sequence when there are ties, while DENSE_RANK does not. These functions help organize and analyze data by position or priority.

Why it matters

Without ranking functions like RANK and DENSE_RANK, it would be hard to assign positions to rows when multiple rows share the same value. This makes it difficult to analyze data like leaderboards, sales rankings, or any ordered list with ties. These functions solve the problem of ranking with ties clearly and consistently, enabling better data insights and decisions.

Where it fits

Before learning RANK and DENSE_RANK, you should understand basic SQL SELECT queries, ORDER BY clauses, and the concept of window functions. After mastering these, you can explore other ranking functions like ROW_NUMBER and NTILE, and advanced window function uses.

Mental Model

Core Idea

RANK and DENSE_RANK assign positions to rows ordered by a column, but RANK skips numbers after ties while DENSE_RANK does not.

Think of it like...

Imagine runners finishing a race. If two runners tie for second place, RANK says both are 2nd, then the next is 4th (skipping 3rd). DENSE_RANK says both are 2nd, and the next is 3rd, with no gaps.

Ranking Example:

Value: 100, 90, 90, 80

RANK:      1,  2,  2,  4
DENSE_RANK:1,  2,  2,  3

Build-Up - 7 Steps

1

Foundation

Understanding Basic Ranking Concept

Concept: Ranking means assigning a position number to each row based on order.

Imagine you have a list of scores: 100, 90, 80. Ranking them means giving 100 the rank 1, 90 the rank 2, and 80 the rank 3. This helps identify who is first, second, and third.

Result

Each score gets a unique rank number based on its order.

Understanding ranking is key to organizing data by importance or order.

2

Foundation

Introducing Ties in Ranking

Concept: Sometimes multiple rows have the same value, causing ties in ranking.

If two scores are both 90, they tie for second place. How do we assign ranks? This introduces the need for special ranking functions that handle ties.

Result

Ties require careful ranking to reflect equal positions.

Recognizing ties is essential to correctly rank data with equal values.

3

Intermediate

How RANK Handles Ties

🤔Before reading on: do you think RANK skips numbers after ties or not? Commit to your answer.

Concept: RANK assigns the same rank to tied rows but skips subsequent ranks accordingly.

For values 100, 90, 90, 80, RANK assigns ranks as 1, 2, 2, 4. Notice it skips rank 3 after the tie at rank 2.

Result

Ranks with gaps appear after ties.

Knowing RANK skips numbers helps understand why gaps appear in ranked lists.

4

Intermediate

How DENSE_RANK Handles Ties

🤔Before reading on: does DENSE_RANK skip ranks after ties or assign consecutive ranks? Commit to your answer.

Concept: DENSE_RANK assigns the same rank to tied rows but does not skip ranks after ties.

For values 100, 90, 90, 80, DENSE_RANK assigns ranks as 1, 2, 2, 3. No ranks are skipped.

Result

Ranks are consecutive even after ties.

Understanding DENSE_RANK prevents confusion about missing ranks in ordered data.

5

Intermediate

Using RANK and DENSE_RANK in SQL

Concept: Both functions are used as window functions with OVER clause to rank rows.

Example SQL: SELECT score, RANK() OVER (ORDER BY score DESC) AS rank, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank FROM scores;

Result

Each row shows score, rank with gaps, and dense_rank without gaps.

Seeing both functions side-by-side clarifies their difference in practice.

6

Advanced

When to Use RANK vs DENSE_RANK

🤔Before reading on: which ranking function would you use for a leaderboard with ties? Commit to your answer.

Concept: Choosing between RANK and DENSE_RANK depends on whether you want gaps in ranking or not.

Use RANK when you want to reflect the actual position including gaps (e.g., official race results). Use DENSE_RANK when you want continuous ranking without gaps (e.g., grouping by priority).

Result

Appropriate ranking function improves data interpretation.

Knowing when to use each function helps present data meaningfully.

7

Expert

Performance and Edge Cases of Ranking Functions

🤔Before reading on: do you think RANK and DENSE_RANK have different performance or internal behavior? Commit to your answer.

Concept: Both functions have similar performance but subtle differences in handling NULLs and ordering can affect results.

Ranking functions process data in order and assign ranks. NULL values are treated as lowest or highest depending on ORDER BY. Understanding this helps avoid unexpected rankings. Also, large datasets with many ties can affect query performance.

Result

Ranking functions behave consistently but require careful ORDER BY and NULL handling.

Understanding internal behavior and edge cases prevents bugs and performance issues in production.

Under the Hood

RANK and DENSE_RANK are window functions that scan ordered rows and assign rank numbers. When they encounter ties, RANK assigns the same rank to tied rows and skips subsequent ranks equal to the number of tied rows minus one. DENSE_RANK assigns the same rank but continues numbering without gaps. Internally, the database engine tracks the current rank and increments it differently based on ties.

Why designed this way?

These functions were designed to handle real-world ranking scenarios where ties occur. RANK reflects official positions with gaps, useful in competitions. DENSE_RANK provides compact ranking useful in grouping and categorization. The design balances clarity and flexibility for different use cases.

Rows ordered by value:
┌───────┬───────────┐
│ Value │ Position  │
├───────┼───────────┤
│ 100   │ Rank=1    │
│ 90    │ Rank=2    │
│ 90    │ Rank=2    │
│ 80    │ Rank=4    │

RANK increments position by count of ties.

DENSE_RANK increments position by 1 regardless of ties.

Myth Busters - 4 Common Misconceptions

Quick: Does RANK assign unique ranks to all rows even with ties? Commit yes or no.

Common Belief: RANK always assigns unique ranks to each row, no duplicates.

Reality: RANK assigns the same rank to tied rows, so duplicates occur.

Why it matters: Assuming unique ranks causes errors in interpreting tied positions and can lead to wrong conclusions.

Quick: Does DENSE_RANK skip numbers after ties like RANK? Commit yes or no.

Common Belief: DENSE_RANK skips rank numbers after ties just like RANK.

Reality: DENSE_RANK does not skip numbers; it assigns consecutive ranks even after ties.

Why it matters: Confusing this leads to misreading ranking sequences and incorrect data grouping.

Quick: Are RANK and DENSE_RANK interchangeable in all cases? Commit yes or no.

Common Belief: RANK and DENSE_RANK can be used interchangeably without affecting results.

Reality: They produce different ranking sequences when ties exist, so choice affects output.

Why it matters: Using the wrong function can misrepresent data order and affect business decisions.

Quick: Does the presence of NULL values affect RANK and DENSE_RANK the same way? Commit yes or no.

Common Belief: NULL values are ignored or treated the same by both functions.

Reality: NULLs are ranked based on ORDER BY rules and can appear first or last, affecting ranks differently.

Why it matters: Ignoring NULL handling can cause unexpected rank assignments and bugs.

Expert Zone

1

RANK can create gaps that reflect real-world positions, which is important in official rankings like sports results.

2

DENSE_RANK is preferred in grouping scenarios where continuous numbering is needed without gaps, such as categorizing priority levels.

3

Handling NULLs in ORDER BY affects ranking results; explicit NULLS FIRST or LAST clauses can control this behavior.

When NOT to use

Avoid using RANK or DENSE_RANK when you need a unique sequential number for each row regardless of ties; use ROW_NUMBER instead. Also, for distributing rows evenly into groups, use NTILE. For very large datasets with complex ties, consider pre-aggregating data to improve performance.

Production Patterns

In production, RANK is used for leaderboards where official positions matter, showing gaps after ties. DENSE_RANK is used in reporting to assign dense categories without gaps. Both are combined with PARTITION BY to rank within groups, such as ranking salespeople within regions.

Connections

ROW_NUMBER

Related ranking function with different tie handling

Understanding RANK and DENSE_RANK clarifies why ROW_NUMBER assigns unique numbers even for ties, useful for distinct row identification.

Sorting Algorithms

Ranking depends on sorted order of data

Knowing how sorting works helps understand how ranking functions assign positions based on ordered data.

Sports Competition Scoring

Real-world example of ranking with ties

Recognizing how sports handle ties in rankings helps grasp why RANK skips numbers and DENSE_RANK does not.

Common Pitfalls

#1 Using RANK when you want continuous ranks without gaps.

Wrong approach:

SELECT score, RANK() OVER (ORDER BY score DESC) AS rank FROM scores;

Correct approach:

SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank FROM scores;

Root cause:

Misunderstanding that RANK creates gaps after ties, which may not be desired.

#2 Assuming RANK and DENSE_RANK produce the same results.

Wrong approach:

Using either function interchangeably without checking output.

Correct approach:

Choose RANK or DENSE_RANK based on whether gaps in ranking are acceptable.

Root cause:

Not knowing the difference in how ties affect ranking sequences.

#3 Not specifying ORDER BY in OVER clause, leading to unpredictable ranks.

Wrong approach:

SELECT score, RANK() OVER () AS rank FROM scores;

Correct approach:

SELECT score, RANK() OVER (ORDER BY score DESC) AS rank FROM scores;

Root cause:

Forgetting that ranking functions require ORDER BY to define ranking order.

Key Takeaways

RANK and DENSE_RANK assign ranks to rows ordered by a column but differ in handling ties.

RANK assigns the same rank to tied rows and skips subsequent ranks, creating gaps.

DENSE_RANK assigns the same rank to tied rows but continues numbering without gaps.

Choosing between RANK and DENSE_RANK depends on whether gaps in ranking are meaningful for your data.

Proper use of these functions improves data analysis and prevents misinterpretation of ordered results.