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.
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
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
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
