Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server 2005 and 2008 Ranking Functions DENSE_RANK and NTILE


By:   |   Read Comments (1)   |   Related Tips: More > Functions - System

Problem
SQL Server introduced four new ranking functions either to rank records in a result-set or to rank records within groups of records of a result-set. With these new functions, we are no longer required to write several lines of code to get ranking data. It does not only help in simplifying the query but also improves the performance of the query. So now the questions are: what are these ranking functions, how they work and how they differ from each others?

Solution
In my last tip (Ranking Functions in SQL Server 2005 and 2008 - Part 1) of this series I discussed the ROW_NUMBER and RANK ranking functions. In this tip, I am going to discuss the DENSE_RANK and NTILE ranking functions, how they work and how they differ from each other.

DENSE_RANK()
As you have seen in the last example of my last tip on this series, the RANK function does not give the consecutive ranking number, so what if you have a requirement to have ranks but no skipping in between?   In other words, you want to have consecutive ranking within the result-set or within partitions (groups of records).  For this kind of scenario you can use DENSE_RANK function, which has the same syntax as of RANK function but unlike the RANK function it gives consecutive ranking. So next record will get a new rank which would be (previous rank + 1). All the examples in this tip will run on AdventureWorks database.

DENSE_RANK() OVER ( [PARTITION BY partition_value_expression , ... [ n ]] ORDER BY order_value_expression , ... [ n ])

Script #4 - DENSE_RANK Function

--This script assigns a consecutive rank to each row of result-set which 
--is ordered by Title column. If two or more records happen to have 
--same value for Title Column they will get the same rank
SELECT DENSE_RANK()  OVER 
   
ORDER BY TitleAS [RecordRank],
LoginIDManagerIDTitleBirthDateMaritalStatusGender 
FROM HumanResources.Employee

--This script assign consecutive rank to each row of a partition 
--(group of records) which is partitioned by the Gender column value and 
--which is ordered by the Title column. If two or more records happen to 
--have same value for Title Column in the partition they will get the same rank
SELECT DENSE_RANK()  OVER 
   
PARTITION BY Gender
   
ORDER BY TITLEAS [RecordRank],
LoginIDManagerIDTitleBirthDateMaritalStatusGender 
FROM HumanResources.Employee

NTILE()
The NTILE function differs from other ranking functions in terms of how it breaks the result-set or partitions (groups of records) into specified number of sub-partitions (sub-groups of records). For each row in the sub partition (sub group) it returns the number of the sub partition (sub group) in which the row falls. If the NTILE can not divide the rows evenly to the specified number of sub groups, then larger sub groups will come before smaller groups. For example, lets assume you have 11 records in a table and you use NTILE with parameter value of 2, then NTILE will divide rows into two sub-groups. The first sub group will have 6 records whereas the second sub group will have 5 records only.

The syntax of NTILE also differs in terms of how it accepts a positive integer (of type int or bigint) constant expression that specifies the number of sub partitions (sub groups) into which each partition (group of records) must be divided.

NTILE (<integer_constant_expression>)  OVER ( [PARTITION BY partition_value_expression , ... [ n ]] ORDER BY order_value_expression , ... [ n ])

Script #4 - NTILE Function

--This script divides the result-set which is considered 
--to be a single partition into 50 sub partitions 
--Because this table has 290 records, first 40 sub partitions 
--will have 6 records whereas the last 10 sub partitions will 
--have 5 records each, hence total = (40*6) + (10*5)
SELECT NTILE(50)  OVER 
   
ORDER BY TitleAS [SubPartition#],
LoginIDManagerIDTitleBirthDateMaritalStatusGender 
FROM HumanResources.Employee

 

--This script divides each partition, which has been partitioned 
--on Gender column, into 50 sub partitions 
--Because the first partition for "F" has 84 records, first 34 sub partitions 
--will have 2 records whereas the last 16 sub partitions will have 1 records each, 
--hence total = (34*2) + (16*1), the same rule applies for other partitions as well
SELECT NTILE(50OVER 
   
PARTITION BY Gender
   
ORDER BY TITLEAS [SubPartition#],
LoginIDManagerIDTitleBirthDateMaritalStatusGender 
FROM HumanResources.Employee

 

Common Table Expression (CTE)
With Common Table Expression you basically create a derived table which is available within the scope of a single, SELECT, INSERT, UPDATE, DELETE or MERGE statement. It helps you to simplify the process of achieving the same goal for which you had to write complex/recursive queries in prior versions of SQL Server. Many times you might be required to use ranking functions along with a CTE. For example you can combine these two commands to delete duplicate records from a table with just one query. Refer "Different strategies for removing duplicate records in SQL Server" for more details. For more tips on Common Table Expressions click here. For details about Common Table Expressions on msdn, click here.

Next Steps



Last Update:






About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Thursday, April 07, 2011 - 10:21:52 AM - Eric Sacramento Back To Top

Nice article ! It is better than MS articles and books !!

Congratulations.


Learn more about SQL Server tools