In my series of essays about SQL Server's ranking functions, this is the last one. The Row Number, Rank, and DenseRank() functions were covered in earlier talks. It's time to finally talk about the idea behind the NTile() method. The following will be the first data setup for the discussion.
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.
HostForLIFEASP.NET SQL Server 2022 Hosting
