RSS Feed

SQL NTILE()


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:

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.