Overview
The topic of this part is about ranking functions. They were introduced in SQL Server 2005. ROW_NUMBER is also a ranking function, but it has already been covered extensively in the previous part of this tutorial.
Explanation
RANK and DENSE_RANK
In contrast with the ROW_NUMBER function, RANK and DENSE_RANK don’t have to generate unique numbers. The difference between all these functions is how they handle ties.
- ROW_NUMBER will always generate unique values without any gaps, even if there are ties.
- RANK can have gaps in its sequence and when values are the same, they get the same rank.
- DENSE_RANK also returns the same rank for ties, but doesn’t have any gaps in the sequence.
Let’s illustrate with an example. Suppose we have the following sample values:
We can write the following query to test the three ranking functions:
SELECT
[Group]
,[Value]
,RowNumber = ROW_NUMBER() OVER (PARTITION BY [Group] ORDER BY [Value])
,[Rank] = RANK() OVER (PARTITION BY [Group] ORDER BY [Value])
,[DenseRank] = DENSE_RANK() OVER (PARTITION BY [Group] ORDER BY [Value])
FROM [dbo].[Test];
This gives us this result set:
As you can see, RANK will return the same rank for ties. However, the next rank in group A is the number 3, omitting rank 2. DENSE_RANK on the other hand doesn’t skip rank 2. When there are no ties in the record set, all three ranking functions return the exact same result.
With ROW_NUMBER and RANK, we can create an alternative construct to detect duplicate rows. With the same sample data used in part 5 of this tutorial, we can create the following query to find all rows that have a duplicate business key:
SELECT
[EmployeeKey]
,[EmployeeName]
,[InsertDate]
,RID = ROW_NUMBER() OVER (ORDER BY [EmployeeKey])
,RankID = RANK() OVER (ORDER BY [EmployeeKey])
FROM [dbo].[EmployeesDuplicate];
The result set:
Now we only need to filter on records where the generated row number (RID) is different from the rank (RankID). This will return a record for Bob, so we know there’s a duplicate record for that employee.
When to use RANK or DENSE_RANK?
As specified before, RANK can have gaps in the sequence and DENSE_RANK doesn’t. Suppose you have the task to find the top 5 customers according to sales value. With RANK, you can rank your customers and then filter out any customers with a rank bigger than 5. If you would do the same with DENSE_RANK, you might end up with more than 6 customers!
Suppose we have this set of data about our customers:
When adding ranks with RANK and DENSE_RANK, we have the following data set:
When filtering on rank <= 5, you’ll get exactly 5 rows returned. However, if you filter on denserank <= 5, you’ll get 6 rows, which might not be what you want. The more ties, the more rows DENSE_RANK can return. If the tie would be on exactly row number 5, the RANK function would also return 6 rows. In that case, you need to “break the tie”. You can do this by using the ROW_NUMBER function or by sorting on additional column.
NTILE
The last ranking function is a bit special. NTILE divides the rows in roughly equal sized buckets. Suppose you have 20 rows and you specify NTILE(2). This will give you 2 buckets with 10 rows each. When using NTILE(3), you get 2 buckets with 7 rows and 1 bucket with 6 rows.
Let’s reuse the query from the previous section, but add an expression using NTILE:
SELECT
[Group]
,[Value]
,RowNumber = ROW_NUMBER() OVER (PARTITION BY [Group] ORDER BY [Value])
,[Rank] = RANK() OVER (PARTITION BY [Group] ORDER BY [Value])
,[DenseRank] = DENSE_RANK() OVER (PARTITION BY [Group] ORDER BY [Value])
,Buckets = NTILE(2) OVER (PARTITION BY [Group] ORDER BY [Value])
FROM [dbo].[Test];
We get the following results:
An example where NTILE was used to divide data into buckets is described in the tip How to create a heat map graph in SQL Server Reporting Services 2016.
Additional Information
- More tips about the ranking functions:
Koen Verbeeck is a seasoned business intelligence consultant with over a decade of experience with the Microsoft Data Platform. He holds several certifications, including Azure Data Engineer. He’s a prolific writer, with over 375 articles on technologies such as Microsoft Fabric, SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at various events such as PASS, SQLBits, dataMinds Connect and many others. He frequently delivers educational webinars on MSSQLTips.com. For his efforts, Koen has been awarded the Microsoft MVP data platform award for many years.
- MSSQLTips Awards:
- Leadership Award (200+ Tips) – 2021
- Author of the Year – 2014/2020/2022
- Author Contender – 2024/2025
