NTile Function in SQL Server

This is the final article in the series of my articles related to the ranking functions in SQL Server. In previous discussions, we discussed the Row Number, Rank and DenseRank() functions. Finally its time to discuss the concept of the NTile() function. Our initial data setup for the discussion will be like the following:

 

 

The NTile function

 

The NTile function takes an integer as an input and divides the records of the result set into that number of groups. For example, NTile(5) will divide a result set of 10 records into 5 groups with two records in each group. In case the result set is not exactly divisible into an equal number of records, it assigns more records to the starting groups and less to the following ones.

 

For example, if we have 11 records and we apply NTile(5), then it will divide the result set into 5 groups with 3 records in the first group and 2 records in the rest of the 4 groups.

 

The main point is that within each group, each record will have the same number or rank.

 

Again, we will be discussing the concept with two cases.

 

Case 1: Ranking entire result set using Order By clause

 

Our entire query will remain the same except the ranking function is changed to NTile and we will divide the result set into 4 groups.

SELECT NTile(4) OVER (ORDER BY DepartmentName DESC) AS GeneratedRank, MemberName, Gender, DepartmentName FROM dbo.DepartmentMembers

Execute the query and see the results. It divides the 9 records into 4 groups with 3 records in the first group and 2 records in the rest of the 3 groups. Within each group, the records have the same rank or number assigned to them.

 

 

 

It might seem quite similar to the Dense_Rank, but it is not. Dense_Rank orders the data by the column specified in the order by clause and then assigns the rank. But here, the order by clause only matters for the starting point of dividing the result set into groups. The results are divided into groups, depending on the size provided by the function parameter and the ranking is assigned to the records based on the group number. See the queries below that compares both the functions on the same data:

 

 

 

Case 2: Ranking partitioned result set using Partition By Clause

The query remains the same, except the Partition By clause is also added. So the query changes to:

SELECT NTILE(4) OVER (PARTITION BY Gender ORDER BY DepartmentName DESC) AS GeneratedRank, MemberName, Gender, DepartmentName
FROM dbo.DepartmentMembers

This time, the result set is first partitioned into 2 result sets based on Gender and then within each result set, the results are further divided by the NTile function dependeing on the size defined. The logic of dividing each result set remains the same as we discussed above. the only difference is that in case 1, it was applied on the entire results set, but in this case, it is applied into two result sets 1 and 2. See the results below:

 

 

 

As we can see above, the entire result set was divided into two sets, based on the partition by Gender. Further, within each result set, they are divided into 4 groups. For result set 1, we have 5 records. So the first group in the first result set is divided into 2 records and the remaining 3 records are divided into 1 record each. For result set 2, the record set is divided into 4 equal groups of 1 record each, since there was a total of 4 records.

 

So this was about the NTile function in SQL Server. I hope you enjoyed reading this.