SQL SERVER

NTILE function in SQL Server

NTILE function

  • Introduced in SQL Server 2005
  • ORDER BY Clause is required
  • PARTITION BY clause is optional
  • Distributes the rows into a specified number of groups
  • If the number of rows is not divisible by number of groups, you may have groups of two different sizes.
  • Larger groups come before smaller groups

For example

  • NTILE(2) of 10 rows divides the rows in 2 Groups (5 in each group)
  • NTILE(3) of 10 rows divides the rows in 3 Groups (4 in first group, 3 in 2nd & 3rd group)

Syntax : NTILE (Number_of_Groups) OVER (ORDER BY Col1, Col2, …)

We will use the following Employees table for the examples in this video.
Employees Table

SQL Script to create Employees table Create Table Employees (     Id int primary key,     Name nvarchar(50),     Gender nvarchar(10),     Salary int ) Go
Insert Into Employees Values (1, ‘Mark’, ‘Male’, 5000) Insert Into Employees Values (2, ‘John’, ‘Male’, 4500) Insert Into Employees Values (3, ‘Pam’, ‘Female’, 5500) Insert Into Employees Values (4, ‘Sara’, ‘Female’, 4000) Insert Into Employees Values (5, ‘Todd’, ‘Male’, 3500) Insert Into Employees Values (6, ‘Mary’, ‘Female’, 5000) Insert Into Employees Values (7, ‘Ben’, ‘Male’, 6500) Insert Into Employees Values (8, ‘Jodi’, ‘Female’, 7000) Insert Into Employees Values (9, ‘Tom’, ‘Male’, 5500) Insert Into Employees Values (10, ‘Ron’, ‘Male’, 5000) Go

NTILE function without PARTITION BY clause : Divides the 10 rows into 3 groups. 4 rows in first group, 3 rows in the 2nd & 3rd group.

SELECT Name, Gender, Salary, NTILE(3) OVER (ORDER BY Salary) AS [Ntile] FROM Employees

ntile function in sql server 2008 with example

What if the specified number of groups is GREATER THAN the number of rows
NTILE function will try to create as many groups as possible with one row in each group. 

With 10 rows in the table, NTILE(11) will create 10 groups with 1 row in each group.

SELECT Name, Gender, Salary, NTILE(11) OVER (ORDER BY Salary) AS [Ntile] FROM Employees

sql server ntile example

NTILE function with PARTITION BY clause : When the data is partitioned, NTILE function creates the specified number of groups with in each partition.

The following query partitions the data into 2 partitions (Male & Female). NTILE(3) creates 3 groups in each of the partitions.

SELECT Name, Gender, Salary, NTILE(3) OVER (PARTITION BY GENDER ORDER BY Salary) AS [Ntile] FROM Employees

sql server ntile function with partition by clause

About the author

shohal

Leave a Comment