SQL Commands


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

 

BookMark This Page




EnglishFrenchGermanItalianPortugueseRussianSpanish