NTILE is a ranking function in SQL Server to distribute rows into a specified number of groups and assign a number to each group in incremental order starting with One. NTILE function works with ORDER BY clause to sort the rows in the specified order and splits them into groups.
Let us assume we have 10 rows in a table and using NTILE(5) function. Considering we’ve even number of rows in each group as 10 divided by NTILE(5) comes to 2 rows in each group. Thus NTILE function will distribute 2 rows in each group and assign them a number from 1 to 5.
In case of an odd number of rows in a table. Assuming we have 11 rows and using NTILE(5) function. NTILE function determines the number of rows in a table before splitting them into groups. Count of rows in a table divided by the number of groups specified with NTILE create groups with an unequal number of rows. Groups with a bigger number of rows will be listed ahead of groups with a small number of rows.
In case we have 11 rows divided by NTILE(5) comes to 3 rows in the first group followed by 2 rows in rest of the four groups.
Let’s run through with few examples demonstrating use of NTILE function.
1.
NTILE – On Even Number Of Rows In Table
In the following example, we have created dbo.Student table with 10 rows having Id, Name and Percentage columns. Now using NTILE(5) function splits 10 rows into 5 groups and ordering the result by Percentage column. We have 10 rows and creating 5 groups using NTILE results in 2 rows in each group because 10 divided by NTILE(5) is 2 rows in each group.
--NTILE On Even Number Of Rows In Table USE tempdb; IF OBJECT_ID('dbo.Student', 'U') IS NOT NULL DROP TABLE dbo.Student; GO CREATE TABLE dbo.Student ( ID INT IDENTITY(1,1) PRIMARY KEY ,Name VARCHAR(255) ,Percentage INT ); INSERT INTO dbo.Student VALUES ('Atul' ,90), ('Vishal' ,91), ('Shailesh',97), ('Niraj' ,92), ('Chetan' ,89), ('Sangram' ,87), ('Rohit' ,87), ('Prashant',93), ('Ravi' ,91), ('Akansha' ,94); --Creates 5 groups with Even number of rows in each group SELECT ID, Name, Percentage, NTILE(5) OVER(ORDER BY Percentage DESC) as NtileGroup FROM Student;
Following is the result set.
2.
NTILE – On Odd Number Of Rows In Table
In the following example, we have created dbo.Student table with 11 rows having Id, Name and Percentage columns. Using NTILE(5) function on 11 rows results in 3 rows in the first group and 2 rows in the rest of the 4 groups. Groups with a bigger number of rows will be listed ahead of groups with a small number of rows. We’ve ordered the result by Percentage column in NTILE function.
--NTILE On Odd Number Of Rows In Table USE tempdb; IF OBJECT_ID('dbo.Student', 'U') IS NOT NULL DROP TABLE dbo.Student; GO CREATE TABLE dbo.Student ( ID INT IDENTITY(1,1) PRIMARY KEY ,Name VARCHAR(255) ,Percentage INT ); INSERT INTO dbo.Student VALUES ('Atul' , 90), ('Vishal' , 91), ('Shailesh', 97), ('Niraj' , 92), ('Chetan' , 89), ('Sangram' , 87), ('Rohit' , 87), ('Prashant', 93), ('Ravi' , 91), ('Akansha' , 94), ('Falak' , 86); -- Added Extra Row --Creates first group with 3 rows and four group with 2 rows each. SELECT ID, Name, Percentage, NTILE(5) OVER(ORDER BY Percentage DESC) as NtileGroup FROM Student;
Following is the Result Set.
3.
NTILE With Partition By Clause
Following example demonstrates the use of NTILE function with partition by clause. Most importantly this example uses partition by to divide the rows into different groups based on Subject column. Later applying NTILE(2) function to create groups inside each partition. Therefore we have created two partitions by Subject, “English” and “Math” then applying NTILE function to each partition dividing 6 rows into 2 different groups of 3 rows using NTILE(2) function.
--NTILE With Partition By Clause USE tempdb; IF OBJECT_ID('dbo.Student', 'U') IS NOT NULL DROP TABLE dbo.Student; GO CREATE TABLE dbo.Student ( ID INT IDENTITY(1,1) PRIMARY KEY ,Name VARCHAR(255) ,Subject VARCHAR(20) ,Marks INT ); INSERT INTO dbo.Student VALUES ('Atul' ,'English' ,90), ('Vishal' ,'English' ,91), ('Shailesh','English' ,97), ('Niraj' ,'English' ,92), ('Chetan' ,'English' ,89), ('Sangram' ,'English' ,87), ('Rohit' ,'Math' ,87), ('Prashant','Math' ,93), ('Ravi' ,'Math' ,91), ('Akansha' ,'Math' ,94), ('Falak' ,'Math' ,86), ('Avni' ,'Math' ,73); DECLARE @NumOfGroups as INT = 2; SELECT ID, Name, Subject, Marks, NTILE(@NumOfGroups) OVER(PARTITION BY Subject ORDER BY Marks DESC) as NtileGroup FROM Student;
Result Set
4.
NTILE With Partition By Clause With Uneven Rows
We’ve created two partitions using Subject “English” and “Math” having 7 rows in each partition. Besides we apply NTILE(2) function to each individual partition of an odd number of rows, therefore, it creates two unequal groups. The first group has 4 rows and the second group has 3 rows in both the partition as per the rule, the bigger number of rows are placed in the first group followed by the lower number of rows in later groups.
--NTILE With Partition By Clause On Uneven Rows USE tempdb; IF OBJECT_ID('dbo.Student', 'U') IS NOT NULL DROP TABLE dbo.Student; GO CREATE TABLE dbo.Student ( ID INT IDENTITY(1,1) PRIMARY KEY ,Name VARCHAR(255) ,Subject VARCHAR(20) ,Marks INT ); INSERT INTO dbo.Student VALUES ('Atul' ,'English' ,90), ('Vishal' ,'English' ,91), ('Shailesh','English' ,97), ('Niraj' ,'English' ,92), ('Chetan' ,'English' ,89), ('Sangram' ,'English' ,87), ('Aadesh' ,'English' ,83), ('Rohit' ,'Math' ,87), ('Prashant','Math' ,93), ('Ravi' ,'Math' ,91), ('Akansha' ,'Math' ,94), ('Falak' ,'Math' ,86), ('Avni' ,'Math' ,73), ('Ranjana' ,'Math' ,94); DECLARE @NumOfGroups as INT = 2; SELECT ID, Name, Subject, Marks, NTILE(@NumOfGroups) OVER(PARTITION BY Subject ORDER BY Marks DESC) as NtileGroup FROM Student;
Result Set
Summary
In this tech-recipes post we’ve learnt how to use NTILE ranking function in SQL Server to split rows into different buckets and number them in increasing order. Besides we’ve used NTILE function on uneven rows with Partition by clause. If you like this post you may browse through Tech-Recipes Database Archive posts to learn more useful stuff.
