The Window Ranking functions: ROW_NUMBER(), RANK(), and DENSE_RANK() each rank rows with an increasing integer value. I wrote a previous blog post, ROW_NUMBER() Window Function – find duplicate values, where I covered how the ROW_NUMBER() window function can be used to target any duplicate rows, with the use of the PARTITION BY clause in the OVER() clause. In this post, I cover the differences between RANK(), and DENSE_RANK() in handling any ties according to the sorting performed by the ORDER BY clause with regards to the assigned increasing integer. Continue reading and see examples…
OS and DB used:
- Linux Mint 20 “Ulyana”
- MySQL 8.0.22
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
Inspirational Videos
I learned a great deal about RANK() and DENSE_RANK() from these videos so be sure and check them out as well…
An arbitrary table named ‘some_names’ containing duplicate values in the ‘first_name’ column is our test data for the example queries:
+----+------------+
| id | first_name |
+----+------------+
| 1 | Max |
| 2 | Mary |
| 3 | Charlie |
| 4 | Humpty |
| 5 | Roger |
| 6 | Jim |
| 7 | Jupyter |
| 8 | Max |
| 9 | Mary |
| 10 | Charlie |
| 11 | Humpty |
| 12 | Roger |
| 13 | Jim |
| 14 | Jupyter |
| 15 | Jim |
| 16 | Jim |
| 17 | Max |
| 18 | Max |
| 19 | Mary |
+----+------------+
19 rows in set (0.0011 sec)
RANK() and DENSE_RANK() differences: How RANK() and DENSE_RANK() are similar when there are no ties
In order to better understand how RANK() and DENSE_RANK() differ when duplicate values are present in the sorting ORDER BY clause, we will see a query where there are no duplicates. We can easily eliminate duplicates from any query results using DISTINCT on the ‘first_name’ column. I’ll wrap this particular SELECT in a CTE (WITH clause) for easier handling:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | mysql-sql [learning]> WITH distinct_names AS (SELECT DISTINCT first_name FROM some_names) -> SELECT dn.first_name, -> RANK() OVER(ORDER BY dn.first_name ASC) AS rnk, -> DENSE_RANK() OVER(ORDER BY dn.first_name ASC) AS d_rnk -> FROM distinct_names AS dn; +------------+-----+-------+ | first_name | rnk | d_rnk | +------------+-----+-------+ | Charlie | 1 | 1 | | Humpty | 2 | 2 | | Jim | 3 | 3 | | Jupyter | 4 | 4 | | Mary | 5 | 5 | | Max | 6 | 6 | | Roger | 7 | 7 | +------------+-----+-------+ 7 rows in set (0.0329 sec |
When there are no ties in the sorting column(s), both RANK() and DENSE_RANK() assign a sequential integer value. Which is an important distinction because when there are ties (duplicates) in the sorting column, both of these Window functions handle assigning the incrementing integer value different as we will see in the query examples that follow.
RANK() and DENSE_RANK() differences: How RANK() handles ties
First, I’ll execute a query using RANK(), ordering by the ‘first_name’ column in ASC (ascending) order:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | mysql-sql [learning]> SELECT id, first_name, -> RANK() OVER(ORDER BY first_name ASC) AS rnk -> FROM some_names; +----+------------+-----+ | id | first_name | rnk | +----+------------+-----+ | 3 | Charlie | 1 | | 10 | Charlie | 1 | | 4 | Humpty | 3 | | 11 | Humpty | 3 | | 6 | Jim | 5 | | 13 | Jim | 5 | | 15 | Jim | 5 | | 16 | Jim | 5 | | 7 | Jupyter | 9 | | 14 | Jupyter | 9 | | 2 | Mary | 11 | | 9 | Mary | 11 | | 19 | Mary | 11 | | 1 | Max | 14 | | 8 | Max | 14 | | 17 | Max | 14 | | 18 | Max | 14 | | 5 | Roger | 18 | | 12 | Roger | 18 | +----+------------+-----+ 19 rows in set (0.0013 sec) |
Based on the returned query results, RANK() assigns the same integer value for any duplicate values (ties). For example, both ‘Charlie’ value rows are assigned 1. Likewise, both ‘Jupyter’ values are assigned a 9.
However, the ranking numbers are not sequential as they were in the query where no duplicate rows existed. As any duplicate values are encountered, RANK() skips the next sequential integer value. Consider the gap from value 1 to 3 between the second ‘Charlie’ first_name column value and the first ‘Humpty’ first_name column value. The 2 has been omitted or skipped and the incrementing integer value resumes with 3.
RANK() and DENSE_RANK() differences: How DENSE_RANK() handles ties
How does DENSE_RANK() differ from RANK() with regards to ties?
Essentially, I’ll execute the same query, but using DENSE_RANK() instead of RANK(). Again, the ‘first_name’ column is used as the sort criteria in the ORDER BY clause:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | mysql-sql [learning]> SELECT id, first_name, -> DENSE_RANK() OVER(ORDER BY first_name ASC) AS d_rnk -> FROM some_names; +----+------------+-------+ | id | first_name | d_rnk | +----+------------+-------+ | 3 | Charlie | 1 | | 10 | Charlie | 1 | | 4 | Humpty | 2 | | 11 | Humpty | 2 | | 6 | Jim | 3 | | 13 | Jim | 3 | | 15 | Jim | 3 | | 16 | Jim | 3 | | 7 | Jupyter | 4 | | 14 | Jupyter | 4 | | 2 | Mary | 5 | | 9 | Mary | 5 | | 19 | Mary | 5 | | 1 | Max | 6 | | 8 | Max | 6 | | 17 | Max | 6 | | 18 | Max | 6 | | 5 | Roger | 7 | | 12 | Roger | 7 | +----+------------+-------+ 19 rows in set (0.0013 sec) |
The above query results are different but do share some similarities with the example query using RANK(). As with the RANK() function, duplicate values (or ties) in the ‘first_name’ column are assigned the same integer value by DENSE_RANK().
However, beginning with the same 2 ‘first_name’ column values we discussed in the query using RANK() – ‘Charlie’ and ‘Humpty’ – the incrementing value continues sequentially with the next number, 3, being assigned to the ‘Jim’ first_name column value.
Where the second ‘Humpty’ row (with ‘id’ column value of 4) had a RANK() value of 3, using DENSE_RANK() yields 2. Which is the next sequential value after 1’s are assigned to both ‘Charlie’ first_name rows.
RANK() and DENSE_RANK() differences: Side by side
A side-by-side comparison query using both RANK() and DENSE_RANK() helps put in perspective the differences in the sequential number ranking when ties are encountered:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | mysql-sql [learning]> SELECT id, first_name, -> RANK() OVER(ORDER BY first_name ASC) AS rnk, -> DENSE_RANK() OVER(ORDER BY first_name ASC) AS d_rnk -> FROM some_names; +----+------------+-----+-------+ | id | first_name | rnk | d_rnk | +----+------------+-----+-------+ | 3 | Charlie | 1 | 1 | | 10 | Charlie | 1 | 1 | | 4 | Humpty | 3 | 2 | | 11 | Humpty | 3 | 2 | | 6 | Jim | 5 | 3 | | 13 | Jim | 5 | 3 | | 15 | Jim | 5 | 3 | | 16 | Jim | 5 | 3 | | 7 | Jupyter | 9 | 4 | | 14 | Jupyter | 9 | 4 | | 2 | Mary | 11 | 5 | | 9 | Mary | 11 | 5 | | 19 | Mary | 11 | 5 | | 1 | Max | 14 | 6 | | 8 | Max | 14 | 6 | | 17 | Max | 14 | 6 | | 18 | Max | 14 | 6 | | 5 | Roger | 18 | 7 | | 12 | Roger | 18 | 7 | +----+------------+-----+-------+ 19 rows in set (0.0016 sec) |
I hope this post has shed light on the differences between RANK() and DENSE_RANK() with how ties are handled and the increasing ranking value is incremented. As always, if you see anything in the code I need to correct or improve on, please let me know via the comments below. Thanks for reading!
Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.