Why RANK and DENSE_RANK difference in SQL? - Purpose & Use Cases

The Big Idea

Ever wondered why some rankings skip numbers after ties? The answer lies in RANK vs DENSE_RANK!

The Scenario

Imagine you have a list of students' test scores on paper and you want to assign ranks to each score. You try to write them down manually, but when two students have the same score, you get confused about how to number their ranks and what to do with the next student's rank.

The Problem

Manually assigning ranks is slow and confusing. You might skip numbers or assign wrong ranks when scores tie. This causes mistakes and wastes time, especially with many scores.

The Solution

Using RANK and DENSE_RANK functions in SQL automatically handles ties correctly. RANK leaves gaps after ties, while DENSE_RANK does not. This makes ranking clear, consistent, and error-free.

Before vs After

✗ Before

Score: 90, Rank: 1
Score: 90, Rank: 1
Score: 85, Rank: 3  -- manual guess

✓ After

SELECT Score, RANK() OVER (ORDER BY Score DESC) AS Rank FROM Scores;
SELECT Score, DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank FROM Scores;

What It Enables

It enables you to quickly and accurately rank data with ties, making analysis and reporting reliable and easy.

Real Life Example

In a sports tournament, players may have the same points. Using RANK and DENSE_RANK helps assign their positions correctly, showing who shares a rank and how the next ranks follow.

Key Takeaways

Manual ranking is confusing and error-prone with ties.

RANK and DENSE_RANK automate ranking and handle ties differently.

RANK leaves gaps after ties; DENSE_RANK does not.