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 compare both 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 depending 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 to 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.