SQL NTILE()
In SQL Server, there are few functions which perform Ranking in the result set, viz. ROW_NUMBER(), RANK(), DENSE_RANK() & NTILE(), and are used to return a ranking value in a partition for each row. In this post we will take a look at NTile() ranking method.
NTile() is used to divide the rows into a specified no. of groups in an ordered partition. Number as numeric parameter can be given to NTILE method. Groups will be numbered starting from one.
Syntax of NTile():
NTILE (Number_Expression) OVER ( [ <partition_clause> ] < Order_Clause > )
Number_Expression : It is a numeric value which decides the no. of groups to be created.
Partition_Clause : It can be given (optional) to create partition with the help of OVER Method which will divide the result given by FROM clause.
Order_Clause : It will be used to create an ordered list which will be further divided into groups.
We have a table named Fruit Category. The table looks like:
Example 1 of NTile():
We will be writing a query to fetch records in 3 Groups partitioned in descending order. Grouping the result into 3, we have given 3 as parameter (Numer_Expression) in NTile method.
Query:
Note: With Ntile(), if we pass 1 as parameter , there will be only one group e.g. 1. So, it’s better to use more than 1 as parameter.
Example 2 of NTile ():
To understand more, I would recommend you to run the above query with 2 as parameter in NTIle method. That query will bring us result with 2 Groups (i.e. 1 & 2). Same no. of records (i.e. 25) will be there. But the only difference is that the records are now divided in 2 groups instead of 3 in example 1.
Example 3 of NTile(): With Partition
Example 1 of NTile():
We will be writing a query to fetch records in 3 Groups partitioned in descending order. Grouping the result into 3, we have given 3 as parameter (Numer_Expression) in NTile method.
Query:
SELECT FruitName, Weight_In_Grams,Participant_Name,
NTILE(3) OVER (
ORDER BY Weight_In_Grams DESC
) AS [Ntile]
FROM dbo.FruitCategory
Output:
With the above query, firstly, records are ordered by Weight_In_Grams column and then the result will be divided into 3 groups (refer column Ntile). This is because we have given 3 as parameter to NTile method.
Note: With Ntile(), if we pass 1 as parameter , there will be only one group e.g. 1. So, it’s better to use more than 1 as parameter.
Example 2 of NTile ():
To understand more, I would recommend you to run the above query with 2 as parameter in NTIle method. That query will bring us result with 2 Groups (i.e. 1 & 2). Same no. of records (i.e. 25) will be there. But the only difference is that the records are now divided in 2 groups instead of 3 in example 1.
SELECT FruitName, Weight_In_Grams,Participant_Name,
NTILE(2) OVER (
ORDER BY Weight_In_Grams DESC
) AS [Ntile]
FROM dbo.FruitCategory
Example 3 of NTile(): With Partition
SELECT FruitName, Weight_In_Grams,Participant_Name,
NTILE(3) OVER (
PARTITION BY FruitName
ORDER BY Weight_In_Grams DESC
) AS [Ntile]
FROM dbo.FruitCategory
In this query, there will be 3 (Number_Expression) groups for each FruitName (table’s column)(refer image). In this, firstly, ordering will be done, & then Groups (as 3 No.) will be created on the basis of FruitName i.e. Apple will have 3 Groups, then Banana will have 3 & last Guava will have 3 groups.