NTILE() Function in SQL Server

NTILE() function in SQL Server is a window function that distributes rows of an ordered partition into a pre-defined number of roughly equal groups. It assigns each group a number_expression ranging from 1. NTILE() function assigns a number_expression for every row in a group, to which the row belongs. 

Syntax :

NTILE(number_expression) OVER (
   [PARTITION BY partition_expression ]
   ORDER BY sort_expression [ASC | DESC]
)

Parameters of syntax in detail :

• number_expression The number_expression is the integer into which the rows are divided.
• PARTITION BY clause The PARTITION BY is optional, it differs the rows of a result set into partitions where the NTILE() function is used.
• ORDER BY clause The ORDER BY clause defines the order of rows in each partition where the NTILE() is used.

When a number of rows aren’t divisible by the number_expression, the NTILE() function results the groups of two sizes with a difference by one. The larger groups always come ahead of the smaller group within the order specified by the ORDER BY within the OVER() clause. Also, when the all of rows are divisible by the number_expression, the function divides evenly the rows among number_expression. 

Example: Let us create a table named geeks_demo :

CREATE TABLE geeks_demo (
ID INT NOT NULL );
INSERT INTO geeks_demo(ID)
VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

Now,

SELECT * 
FROM geeks_demo;

ID
1
2
3
4
5
6
7
8
9
10

1. Use NTILE() function to divide above rows into 3 groups :

SELECT ID,
NTILE (3) OVER (
ORDER BY ID
) Group_number
FROM geeks_demo; 

1. Output :

ID	Group_number
1	1
2	1
3	1
4	1
5	2
6	2
7	2
8	3
9	3
10	3

Use the NTILE() function to distribute rows into 5 groups :

SELECT ID,
NTILE (5) OVER (
ORDER BY ID
) Group_number

FROM geeks_demo; 

Output :

ID	Group_number
1	1
2	1
3	2
4	2
5	3
6	3
7	4
8	4
9	5
10	5

If someone try to run use the NTILE() function without number_expression :

SELECT ID,
NTILE () OVER (
ORDER BY ID
) Group_number

FROM geeks_demo; 

Output: It will throw the below error:

The function 'NTILE' takes exactly 1 argument(s). 

My Personal Notes arrow_drop_up

Save