NTile function in SQL Server


This is final article in the series of my article related to the ranking functions in SQL server. In previous discussions, we discussed the concept of Row_Number, Rank and Dense_Rank functions. Finally its the time to discuss the concept of NTile() function. For better understanding of this function, I would recommend you to go through my very first article of the series here, as it explains the basics of the syntax of the ranking functions.

Our initial data setup for the discussion will be like the following :

Rank function in SQL Server

What is NTile function ?

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 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 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 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 first group and 2 records in rest of the 3 groups. Within each group, the records have the same rank or number assigned to them.

NTile in SQL Server

It might seem quite similar to the Dense_Rank, but it is not. Dense_Rank order the data by the column specified in 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 ranking gets assigned to the records based on the group number. See the queries below which compares both the functions on same data :

NTile in SQL Server

Case 2 – Ranking partitioned result set using Partition By Clause

The query remains the same, except the Partition By clause also gets 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. Only difference is that in case 1, it was applied on the whole results set, but in this case, it is applied into two result sets 1 and 2. See the results below :

NTile in SQL Server

As we can see above, 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 first group in first result set is divided into 2 records and remaining 3 records are divided into 1 record each. For result set 2, record set is divided into 4 equal groups of 1 record each, as there were total 4 records.

So this was about the concept of NTile function in SQL server. Hope you enjoyed reading this…!!!

Advertisements

About Jasminder

.Net developer and blogger by profession, keen to learn new technologies, love nature, music and cricket.
This entry was posted in SQL Server and tagged . Bookmark the permalink.

I have a suggestion..

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s