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 number of rows isn’t divisible by the number_expression, the NTILE() function results the groups of two sizes with the difference by one. The larger groups always come ahead the smaller group within the order specified by the ORDER BY within the OVER() clause. Also, when the all of rows is 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; 

    Output :

    ID Group_number
    1 1
    2 1
    3 1
    4 1
    5 2
    6 2
    7 2
    8 3
    9 3
    10 3
  2. Use the NTILE() function to distribute rows into 5 groups :
    SELECT ID,
    NTILE (3) 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
  3. 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 below error :

    The function 'NTILE' takes exactly 1 argument(s). 
    
My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.