SQL NTILE() Function Example
Let’s look at some examples of NTILE() function in SQL Server to understand it better.
First we will create a table named ‘geeks_demo’
Query:
CREATE TABLE geeks_demo (
ID INT NOT NULL );
INSERT INTO geeks_demo(ID)
VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
Output:
ID |
---|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
Use NTILE() function to divide above rows into 3 groups
Query:
SELECT ID,
NTILE (3) OVER (
ORDER BY ID
) Group_number
FROM geeks_demo;
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
Query:
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 |
Using the NTILE() function without number_expression Example
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).
SQL Server NTILE() Function
SQL NTILE() function is a window function that distributes rows of an ordered partition into a pre-defined number of roughly equal groups.