The RANK and DENSE_RANK window functions in SQL Server are very handy when you need to gather ranking information from your data.
But do you know the difference between the two?
The difference is subtle, but important. This tutorial assumes you know a thing or two about window functions in SQL Server. We’ll be using terms like “window partition”, and “window order clause”. If you don’t know what a window function is in the first place, you should definitely check out my complete introduction to window functions:
SQL Server Window Functions: An introduction for beginners
Also, all the information in this tutorial can be found in the FREE SQL Server Window Functions Ebook!
This FREE EBook discusses absolutely everything you need to know about window functions in SQL Server, including everything discussed in this tutorial. A proper understanding of window functions is essential for anyone looking to enter the field of data science. This eBook will definitely be a great resource for you to reference throughout your career. Get it today!
Without further ado, let’s get into it:
DENSE_RANK considers distinctness, while RANK does not
When determining what rank to assign a row, RANK will look at the number of rows with a lower ordering value than the current row, then add 1 to it.
DENSE_RANK will return the number of rows with a lower distinct ordering value than the current row, then add 1 to it.
Obviously, the word “distinct” is the operative word.
Example of RANK
Take a look at the following data in an Orders table:
Notice the OrderDate column. Let’s say we want to assign each row a rank according to this OrderDate column. The oldest Order will have a rank of 1, the next oldest will have a rank of 2, so on and so forth!
It’s easy enough to write a query using the RANK function. The RANK function does not have any arguments, and it does not support a window frame. Also, a window order clause is required:
Nice, now our Orders are ranked from oldest to newest.
Let’s look at some specific rows. Notice there are three Orders placed on 9/18/2021:
Remember how RANK determines what number to use for a row: “RANK will look at the number of rows with a lower ordering value than the current row, then add 1 to it”.
When SQL Server is evaluating what rank to assign each of these rows, it’s basically asking “How many rows have an OrderDate less than 9/18/2021?”
The answer is 4. Then remember, 1 gets added to that. Last time I checked, 4 + 1 is 5, which is what we have in our ‘Order Rank‘ column for all three rows!
Then a peculiar thing happens. For the next Order (Order # 135 for Greb Donovan), the rank jumps to 8. But why?
Once again, how many rows have an OrderDate that is less than that row?
Looks like it’s 7:
So again, 7 + 1 is 8, which is what we have for the ‘Order Rank‘ value for that row. And it looks like there is another row with that same rank because it has the same date, 9/22/2021.
Then there is another gap in the RANK value for Order # 145 for Wes Watson. Once again, there are 9 rows with a less ordering value, so that row get’s a ‘Order Rank‘ value of 10.
Now let’s add another column that calculates DENSE_RANK so we can see the difference.
Example of DENSE_RANK
We’ll add another column that calculates DENSE_RANK:
Let’s look at the Order for Greb Donovan again:
Remember how DENSE_RANK determines what number to use for a row: “DENSE_RANK will return the number of rows with a lower distinct ordering value than the current row, then add 1 to it.”
The OrderDate for this row is 9/22/2021. So, how many distinct OrderDate values are less than 9/22/2021?
There are 5:
Then remember, we add 1 to that number. If my math is correct, 5 + 1 is 6, which is what we see in the ‘Order Dense Rank‘ column for this row!
Then for the next Order (Order # 140 for Amy Whinehouse), there are once again 5 distinct OrderDate’s that are less than 9/22/2021, so the ‘Order Dense Rank‘ value for that row is also 6.
For the next Order (Order # 145 for Wes Watson), there are 6 distinct OrderDate’s that are less than 9/30/2021, so his ‘Order Dense Rank‘ value is 7.
Get it?
RANK can have gaps while DENSE_RANK cannot
One of the main things you should notice is that it’s possible for RANK to contain gaps while DENSE_RANK cannot.
Sometimes you want to see gaps in your ranking values, but maybe sometimes you don’t. Now you know which function you should use depending on your needs!
Next Steps:
Leave a comment if you found this tutorial helpful!
Also, make sure you…
Download your FREE SQL Server Window Functions EBook!
This FREE EBook discusses absolutely everything you need to know about window functions in SQL Server, including everything discussed in this tutorial. A proper understanding of window functions is essential for anyone looking to enter the field of data science. This eBook will definitely be a great resource for you to reference throughout your career. Get it today!
RANK and DENSE_RANK are only 2 out of 4 ranking window functions we have available to us in Microsoft SQL Server. I have a full tutorial that discusses ALL the ranking functions in great detail. You should know all of them if you want to master window functions. Check it out:
SQL Server Ranking Window Functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE
Also, if you missed the introduction to window functions, it is a MUST-READ. It discusses all the parts involved in a window function that you need to know in order to write a window function query correctly. Don’t skip that tutorial, you can find it here:
SQL Server Window Functions: An introduction for beginners
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
