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.