NTILE function in SQL Server 

In this video we will discuss NTILE function in SQL Server
NTILE function
1. Introduced in SQL Server 2005
2. ORDER BY Clause is required
3. PARTITION BY clause is optional
4. Distributes the rows into a specified number of groups
5. If the number of rows is not divisible by number of groups, you may have groups of two different sizes.
6. Larger groups come before smaller groups
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our RU-vid channel. Hope you can help.
/ @aarvikitchen5572
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, ...)
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
Link for all dot net and sql server video tutorial playlists
www.youtube.co...
Link for slides, code samples and text version of the video
csharp-video-tu...