How to Use NTILE Ranking Function In SQL Server

Posted December 29, 2018 by Vishwanath Dalvi in Database, SQL Server

How To Refresh Intellisense Cache In SQL Server

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.

How To Use NTILE Function In SQL Server

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.

NTILE Function In SQL Server

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.

NTILE Function In SQL Server

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

NTILE Function In SQL Server

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

NTILE Function In SQL Server

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.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

Leave a Reply