MySQL - RANK vs DENSE_RANK vs ROW_NUMBER - Different Tie Handling Approaches
In MySQL, you can utilize three Window Functions RANK(), DENSE_RANK(), and ROW_NUMBER() to add row numbers.
Each one of these ranking functions has a different way of handling ties.
We will prepare some sample data that will clearly illustrate how RANK(), DENSE_RANK(), and ROW_NUMBER() behave differently when it comes to ties in the data set you're analyzing.
With a clear understanding of their similarities and differences, you can choose the right ranking function to use to solve your real world problems.
Example Data
The following table lists students along with their gender and score.
CREATE TABLE StudentScores
(`gender` varchar(1), `student` varchar(8), `score` int)
;
INSERT INTO StudentScores
(`gender`, `student`, `score`)
VALUES
('M', 'Tobi', 92),
('M', 'Tom', 92),
('M', 'Sam', 89),
('M', 'Ron', 100),
('F', 'Mary', 97),
('F', 'Emily', 95),
('F', 'Jennifer', 89),
('F', 'Lori', 89),
('F', 'Audra', 84);
Use
dbfiddle to execute SQL scripts on sample data.
Rank Rows with ROW_NUMBER()
ROW_NUMBER() function always generates a unique ranking number even with ties.
Tobi and Tom's score tie at 92 so they are uniquely ranked as 4 and 5.
Sam, Jennifer, and Lori's score tie at 89 so they are uniquely ranked as 6, 7, and 8.
-- ROW_NUMBER breaks the tie
SELECT *
,ROW_NUMBER() OVER (
ORDER BY score DESC
) AS RowNum
FROM StudentScores
| gender | student | score | RowNum |
|---|---|---|---|
| M | Ron | 100 | 1 |
| F | Mary | 97 | 2 |
| F | Emily | 95 | 3 |
| M | Tobi | 92 | 4 |
| M | Tom | 92 | 5 |
| M | Sam | 89 | 6 |
| F | Jennifer | 89 | 7 |
| F | Lori | 89 | 8 |
| F | Audra | 84 | 9 |
Rank Rows with RANK()
RANK() function keeps the tie and skips numbers.
Tobi and Tom's score tie at 92 so they are both ranked as 4. Rank 5 is skipped.
Sam, Jennifer, and Lori's score tie at 89 so they are all ranked as 6. Rank 7 and 8 are skipped.
-- RANK() keeps the tie and skip numbers
SELECT *
,RANK() OVER (
ORDER BY score DESC
) AS RankNum
FROM StudentScores
| gender | student | score | RankNum |
|---|---|---|---|
| M | Ron | 100 | 1 |
| F | Mary | 97 | 2 |
| F | Emily | 95 | 3 |
| M | Tobi | 92 | 4 |
| M | Tom | 92 | 4 |
| M | Sam | 89 | 6 |
| F | Jennifer | 89 | 6 |
| F | Lori | 89 | 6 |
| F | Audra | 84 | 9 |
Rank Rows with DENSE_RANK()
DENSE_RANK() also keeps the tie, but does NOT skip numbers.
Tobi and Tom's score tie at 92 so they are both ranked as 4. Rank 5 is NOT skipped.
Sam, Jennifer, and Lori's score tie at 89 so they are all ranked as 5. Rank 6 and 7 are NOT skipped.
-- DENSE_RANK() keeps the tie as well, but does NOT skip numbers
SELECT *
,DENSE_RANK() OVER (
ORDER BY score DESC
) AS DenseRankNum
FROM StudentScores
| gender | student | score | DenseRankNum |
|---|---|---|---|
| M | Ron | 100 | 1 |
| F | Mary | 97 | 2 |
| F | Emily | 95 | 3 |
| M | Tobi | 92 | 4 |
| M | Tom | 92 | 4 |
| M | Sam | 89 | 5 |
| F | Jennifer | 89 | 5 |
| F | Lori | 89 | 5 |
| F | Audra | 84 | 6 |
ROW_NUMBER() vs RANK() vs DENSE_RANK() Comparison
Let's output ranking results in the same table to review how they handle ties differently.
SELECT *
,ROW_NUMBER() OVER (
ORDER BY score DESC
) AS RowNum
,RANK() OVER (
ORDER BY score DESC
) AS RankNum
,DENSE_RANK() OVER (
ORDER BY score DESC
) AS DenseRankNum
FROM StudentScores
| gender | student | score | RowNum | RankNum | DenseRankNum |
|---|---|---|---|---|---|
| M | Ron | 100 | 1 | 1 | 1 |
| F | Mary | 97 | 2 | 2 | 2 |
| F | Emily | 95 | 3 | 3 | 3 |
| M | Tobi | 92 | 4 | 4 | 4 |
| M | Tom | 92 | 5 | 4 | 4 |
| M | Sam | 89 | 6 | 6 | 5 |
| F | Jennifer | 89 | 7 | 6 | 5 |
| F | Lori | 89 | 8 | 6 | 5 |
| F | Audra | 84 | 9 | 9 | 6 |
PARTITION BY Examples of ROW_NUMBER(), RANK(), DENSE_RANK()
Notice that we exclude the optional PARTITION BY clause in above examples so the window we applied ranking functions to is the entire table.
Let's partition by gender into 2 windows and then have 3 ranking functions applied into each window.
SELECT *
,ROW_NUMBER() OVER (
PARTITION BY gender ORDER BY score DESC
) AS RowNum
,RANK() OVER (
PARTITION BY gender ORDER BY score DESC
) AS RankNum
,DENSE_RANK() OVER (
PARTITION BY gender ORDER BY score DESC
) AS DenseRankNum
FROM StudentScores
Review ranking results of gender F window and gender M window. You should observe the same behavior.
ROW_NUMBER() always breaks the tie and generates a unique number for each row within the window partion it belongs to.
RANK() keeps the tie and skips numbers. It leaves gaps.
DENSE_RANK() keeps the tie and does NOT skip numbers. It leaves no gaps.
| gender | student | score | RowNum | RankNum | DenseRankNum |
|---|---|---|---|---|---|
| F | Mary | 97 | 1 | 1 | 1 |
| F | Emily | 95 | 2 | 2 | 2 |
| F | Jennifer | 89 | 3 | 3 | 3 |
| F | Lori | 89 | 4 | 3 | 3 |
| F | Audra | 84 | 5 | 5 | 4 |
| M | Ron | 100 | 1 | 1 | 1 |
| M | Tobi | 92 | 2 | 2 | 2 |
| M | Tom | 92 | 3 | 2 | 2 |
| M | Sam | 89 | 4 | 4 | 3 |
Conclusion
Both RANK() and DENSE_RANK() will assign the same ranking to rows that have ties based by the ORDER BY clause.
DENSE_RANK() will always generate a contiguous sequence of ranks like (1,2,3,...) as it does NOT skip numbers.
RANK() will leave gaps after two or more rows with ties as it skips numbers.
ROW_NUMBER() function will always generate a unique ranking number even with ties.
Having a clear understanding on how RANK(), DENSE_RANK(), and ROW_NUMBER() handle ties differently is very important so they can be applied to solve and implement analytic SQL queries.