NTILE function in SQL server (Transact-SQL)

NTILE is one of the ranking function in sql server. It displays the rows in an ordered partition into a specified number of groups. Number of groups can be decided by passing arguments values in NTILE function. If total number of rows are not divisible by passing number of groups in argument area then first group always contains more rows than later groups.

Syntax - NTILE (<integer value>)    OVER ( [ <partition by clause> ] < Order by clause > )

NTILE(<Number of Groups>) function always returns bigInt.

Arguments detail- 
Number of Groups can be an int or bigint. It decides the total number of groups into result set in which each partition must be divided.

Partition by clause divides the result set which is producing a from clause into partitions to which rank function is applied.

Order by clause determines the order in which NTILE values are assigned to the rows in partition.

Important points -
1. If the total number of rows which are producing by a from clause not divisible by number of groups(passed as first argument) then first group always contains more rows than later group.

2. In real time scenario it is used to divide the result set into buckets which are equal to number of groups passed in arguments.

Scenario - Understanding of NTILE function is quite confusing but i will try to explain it clearly as much as possible. Suppose you work on HR management module in your project and based on new requirement you need to divide your department into three groups which should contain as high performance, medium and low salary range respectively. You can implement this scenario by using NTILE ranking function in sql server.

Let's create three tables -
Department

CREATE TABLE [dbo].[Department](

      [DeptId] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,

      [Name] [varchar](20) NULL

)

Insert records -

INSERT INTO [dbo].[Department] ([Name]) VALUES (N'HR')

INSERT INTO [dbo].[Department] ([Name]) VALUES (N'Finance')

Employee

CREATE TABLE [dbo].[Employee](

      [EmpId] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,

      [DepId] [int] foreign key references Department(DeptId) NULL,

      [Name] [varchar](20) NULL

)

Insert records -

INSERT INTO [dbo].[Employee] ([DepId], [Name]) VALUES (1, N'Florian')

INSERT INTO [dbo].[Employee] ([DepId], [Name]) VALUES (1, N'John')

INSERT INTO [dbo].[Employee] ([DepId], [Name]) VALUES (1, N'Rony')

INSERT INTO [dbo].[Employee] ([DepId], [Name]) VALUES (2, N'Damien')

INSERT INTO [dbo].[Employee] ([DepId], [Name]) VALUES (2, N'Michel')

INSERT INTO [dbo].[Employee] ([DepId], [Name]) VALUES (2, N'Miller')

INSERT INTO [dbo].[Employee] ([DepId], [Name]) VALUES (1, N'Keenan')

EmpSalary

CREATE TABLE [dbo].[EmpSalary](

      [Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,

      [EmpId] [int] foreign key references Employee(EmpId) NULL,

      [Salary] [int] NULL

)

Insert records -

INSERT [dbo].[EmpSalary] ([EmpId], [Salary]) VALUES (1, 1000)

INSERT [dbo].[EmpSalary] ([EmpId], [Salary]) VALUES (2, 2000)

INSERT [dbo].[EmpSalary] ([EmpId], [Salary]) VALUES (3, 3000)

INSERT [dbo].[EmpSalary] ([EmpId], [Salary]) VALUES (4, 4000)

INSERT [dbo].[EmpSalary] ([EmpId], [Salary]) VALUES (5, 5000)

INSERT [dbo].[EmpSalary] ([EmpId], [Salary]) VALUES (6, 6000)

INSERT [dbo].[EmpSalary] ([EmpId], [Salary]) VALUES (7, 3000)

Now as per requirement we should divide the salary range for each department as high, medium and low. In order to achieve this, we can write the following query using of NTILE ranking function -

With CTE

As

(

Select d.Name,ES.Salary,NTILE(3)over(Partition by d.Name Order by ES.Salary Desc)As [NTILE]

from Employee E

Inner join Department D on E.DepId=D.DeptId

Inner join EmpSalary ES on E.EmpId=ES.EmpId

)

Select  *from CTE

It will produce the following result set -

As you see, In above query we passed value as 3 into NTILE function, so it is divided the partition into three groups based on total number of rows availabe in each group.

Point to be noted
In first set which is partitioned by Finance department, contains three rows so NTILE function categorized it as 1,2,3 and in second set which is partitioned by HR, contains four rows so NTILE function categorized it as 1,1,2,3(As explained above, if number of rows are not divisible by number of groups then first group always contains more rows than later groups)

Now in the above query, if we change the value from 3 to 2 in NTILE function then result set will be divided into two groups as below -

I wish this will help everyone, Kindly give your suggestion or share your comments about this post.