The SQL Server DENSE_RANK function is a simple window function you should know to help gather meaningful information from your data.
The DENSE_RANK function is one of four ranking window functions we have available to us in Microsoft SQL Server. You should understand all the ranking window functions if you want to be a great database developer. Check out my full tutorial on ranking window functions to learn more:
SQL Server Ranking Window Functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE
In this tutorial, we’re going to discuss what the DENSE_RANK function is and how to use it.
Also, make sure you download your FREE Ebook:
FREE Ebook on SQL Server Window Functions!
This FREE EBook discusses absolutely everything you need to know about window functions in SQL Server, including everything discussed in this tutorial. This EBook will definitely be a great resource for you to reference throughout your career as a data professional. Get it today!
We’ll discuss these topics in this tutorial:
- What is a window function?
- What is the DENSE_RANK window function?
- Setting up some data
- Example of using DENSE_RANK
- What’s the difference between DENSE_RANK and regular RANK?
- Tips and tricks
Let’s get into it.
1. What is a window function?
Before we dive into the DENSE_RANK function, we need to understand what a window function is in the first place.
A window function can be used to combine aggregate/ranking/offset information AND detail information in a single query.
Window functions are excellent for writing a single query that shows ranking information AND detail information in a single query.
To understand the DENSE_RANK, we need to understand terms like “window partition” and “window order clause“. If you don’t know what those are, you should definitely check out my complete introduction to window functions:
SQL Server Window Functions: An introduction for beginners
2. What is the DENSE_RANK window function?
DENSE_RANK will rank rows in a partition, starting with 1, according to an ordering value you specify. DENSE_RANK considers distinctness when determining the rank to assign a row.
The DENSE_RANK function does not take any arguments, and you do not specify a window frame.
One thing you need to make sure you do specify, however, is a window order clause. This makes perfect sense, because how are you supposed to rank things if they aren’t in some kind of order?
3. Setting up some data
To understand how the DENSE_RANK function works, we need to set up some data. If you have been following along with any of the other window function tutorials, you will already have this data set up in your environment.
Run this SELECT statement to see if you already have the data:
SELECT * FROM Orders
The data should look like this:
If you don’t have the data, let’s go ahead and add it:
--Create Orders table CREATE TABLE Orders ( OrderID INT PRIMARY KEY IDENTITY(100,10), CustID INT, ProdID INT, Qty TINYINT, Orderdate DATETIME ) --Populate Orders table with data INSERT INTO Orders(CustID, ProdID, Qty, Orderdate) VALUES (55, 22, 1, '6/1/2021'), (60, 28, 2, '6/6/2021'), (75, 26, 1, '6/13/2021'), (50, 20, 1, '7/1/2021'), (55, 28, 1, '7/6/2021'), (65, 24, 1, '7/14/2021'), (55, 26, 1, '7/18/2021'), (50, 26, 1, '7/24/2021'), (70, 24, 1, '8/6/2021'), (70, 26, 1, '8/6/2021'), (70, 22, 3, '9/1/2021')
The CustID column represents the ID of the customer who made the order, and the ProdID column represents the ID of the product they bought. We don’t need to set up data in those tables for this tutorial. I just want you to understand the data we’re looking at 🙂
Ok, now that we have some data set up, we can show you an example of using DENSE_RANK.
4. Example of using DENSE_RANK
We’ll look at a very simple example of using the DENSE_RANK function
Let’s take a look at the Orders table:
Maybe it would be nice to assign a rank to each of our Orders according to when the order was placed (a.k.a OrderDate). So for example, the first Order in our table would have a rank of 1. The next Order will have a rank of 2. So on and so forth, for all Orders!
We can use the DENSE_RANK function to easily see that information:
Let’s break down what we have here.
- Notice we call the DENSE_RANK function in the SELECT list. You can only call the DENSE_RANK function (or any other window function) in the SELECT list or the ORDER BY clause.
- The DENSE_RANK() call itself does not have any arguments. It does not support using arguments.
- We use the OVER clause in all window functions. One could say it’s the heart of a window function.
- We specify our window order clause within the OVER clause. In our case, we’re ordering the data in our window by OrderDate.
Since we chose not to outline a window partition clause within our OVER clause, the entire result set is considered one large partition, and therefore, the DENSE_RANK function will operate on all rows in the result set.
Not sure what I’m talking about? It’s because you didn’t read the introduction to window functions tutorial!
Two or more rows can have the same DENSE_RANK value
You probably noticed how two of our rows have the same rank. There are two rows that have an ‘Order Rank‘ value of 9. Namely these rows:
These two rows are essentially tied in rank. They both have identical OrderDate values of 8/6/2021. It would not be appropriate for SQL Server to arbitrarily assign one of them a higher rank than the other, so SQL Server gives them the same rank.
Understanding how the DENSE_RANK value is calculated
When determining the rank to assign a row, SQL will look at the number of rows with a lower ordering value than the current row, then add 1 to it.
Remember, with window functions, rows are evaluated one at a time from top to bottom. For example, when SQL is processing Order # 180, it’s finding how how many rows have a lower ordering value than this row. We can see it’s a count of 8 rows:
In other words, in our case, SQL is asking “How many rows have a lower OrderDate value than 8/6/2021?”
The answer is 8.
So when calculating the rank to assign to Order # 180, SQL will take the number of rows with a lower ordering value (8 in our case), then add 1 to it. Last time I checked, that adds up to 9, which is what we have as the DENSE_RANK value!
And folks, the same thing is true of the other row with a DENSE_RANK of 9 (Order # 190). There are still 8 rows with a LOWER ordering value than that row.
5. What’s the difference between DENSE_RANK and regular RANK?
There is another ranking window function called RANK. You might ask yourself, “What’s the difference”?
The difference is that DENSE_RANK will calculate the number of rows with a lower distinct ordering value than the current row, then add 1 to it.
Regular RANK simply counts the number of rows with a less ordering value, then adds 1. Period. It doesn’t care about distinctness.
Let’s add another column to calculate RANK:
Notice the last row, Order # 200, has a different value between the DENSE_RANK and regular RANK columns.
We need to think about distinctness when looking at the OrderDate for Order # 200, which is 9/1/2021.
How many distinct OrderDate values do we see with a lower date than 9/1/2021? The answer is 9:
Last time I checked, 9 + 1 is 10, which is what we see as the DENSE_RANK value for this Order!
Regular RANK doesn’t care about distinctness. It’s basically asking “How many rows have an OrderDate value less than 9/1/2021“? The answer is 10:
Last time I checked, 10+1 is 11, which is what we see in our ‘Order Rank‘ column!
This is exactly why it’s possible to have gaps with the RANK function, but not have gaps with the DENSE_RANK function. Use the one that suits your needs!
6. Tips and tricks
Here is a list of some helpful tips and tricks you know when working with the RANK function:
Tip # 1: The DENSE_RANK function supports a window partition clause
The examples we’ve looked at all did not have a window partition clause, meaning the entire result set is treated as one large partition.
You should know you can definitely specify a PARTITION clause when using DENSE_RANK (or any other window function).
For example, what if we wanted to see the ranks of OrderDates for each customer? In other words, for each customer, we want to see their Orders and how each OrderDate ranks.
It’s easy to add a PARTITION clause to see those details:
Now, we see a breakdown of ranks by customer. Nice!
Tip # 2: Remember, you can only use window functions in the SELECT list or ORDER BY clause.
All window functions can only be used in the SELECT list or the ORDER BY clause.
Tip # 3: Remember the rules about the DENSE_RANK function.
The list of things to remember about the different window functions is long. Some require an argument, while some don’t. Some require a frame, while others don’t. For the DENSE_RANK function, just remember these 3 rules:
- There are no arguments
- A window order clause is mandatory
- It does not support a window frame.
Next steps:
Leave a comment if you found this tutorial helpful!
Don’t forget to download your FREE Ebook:
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!
Now that you understand DENSE_RANK (and also regular RANK) you should learn about the other ranking window functions available to us in Microsoft SQL Server.
You need to understand all the different ranking functions if you want to be a great database developer. Take a look at the full tutorial on all the ranking functions here:
SQL Server Ranking Window Functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE
Also, if you missed the introduction to window functions, it discusses the different aggregate window functions you can use in SQL Server. That tutorial is a MUST-READ, because 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!
