MSSQL NTILE Tutorial
MSSQL NTILE Function splits the rows into a pre-specified number of groups. The first group number starts with one, second with two & so on. For each row, NTILE returns the number of the group to which the row belongs.
MSSQL NTILE Function SYNTAX
NTILE (integer_expression) ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
Integer_expression is a positive integer that specifies the number of groups into which each partition is splitted. partition_by_clause splits the rows generated into different sets.
If partition_by_clause is not specified, then all rows shall be treated as a single group.
The priority of the ranking is determined by order_by_clause. The order_by_clause unlike partition_by_clause is essential in NTILE () function.
MSSQL NTILE Note
If the number of rows in a partition is not fully divisible by integer_expression, this will result into uneven distribution of groups that differ by one member. Larger groups are placed before smaller groups according to the order specified by the OVER clause.
For example if the total number of rows is 38 and the number of groups is three, the first two groups will have 13 rows and the remaining one group will have 12 rows. Else if, the total number of rows is divisible by the number of groups, the rows will be evenly distributed among the groups. For example, if the total number of rows is 39, and there are three groups, each group will contain 13 rows.
MSSQL NTILE FUNCTION EXAMPLE
Database table “Employee”
| Employee ID | Employee Name | Age | Gender | Location | Salary |
| 1001 | Henry | 54 | Male | Moscow | 100000 |
| 1002 | Tina | 36 | Female | Moscow | 80000 |
| 1003 | John | 24 | Male | London | 40000 |
| 1004 | Mile | 31 | Male | London | 70000 |
| 1005 | Tara | 26 | Female | Moscow | 50000 |
| 1006 | Sophie | 29 | Female | London | 60000 |
Below is an example of NTile function without using partition_by_clause from the “Employee” table:
MSSQL NTILE FUNCTION
SELECT EmployeeID, EmployeeName, Gender, Location,
NTILE(2) OVER (order by Gender DESC)AS NTile
FROM Employee;
MSSQL NTILE Function Results
NTile function output without using partition_by_clause will give the following results.
| Employee ID | Employee Name | Age | Gender | Location | Salary |
| 1001 | Henry | 54 | Male | Moscow | 1 |
| 1003 | John | 24 | Male | London | 1 |
| 1004 | Mile | 31 | Male | London | 1 |
| 1002 | Tina | 36 | Female | Moscow | 2 |
| 1005 | Tara | 26 | Female | Moscow | 2 |
| 1006 | Sophie | 29 | Female | London | 2 |
MSSQL NTILE FUNCTION EXAMPLE 2
Example of Ntile function with partition_by_clause from the “Employee” table:
SELECT EmployeeID, EmployeeName, Gender, Location,
NTILE(2) OVER (partition by Location, order by Gender DESC)AS Ntile
FROM Employee;
DENSE_RANK Function Results
NTile function using partition_by_clause will provide the following output results.
| Employee ID | Employee Name | Gender | Location | Salary |
| 1003 | John | Male | London | 1 |
| 1004 | Mile | Male | London | 1 |
| 1006 | Sophie | Female | London | 2 |
| 1001 | Henry | Male | Moscow | 1 |
| 1002 | Tina | Female | Moscow | 1 |
| 1005 | Tara | Female | Moscow | 2 |
