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.FruitCategoryOutput: 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.FruitCategoryExample 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.FruitCategoryIn 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.