Skip to content
Related Articles

Related Articles

NTILE() Function in SQL Server
  • Last Updated : 09 Sep, 2020
GeeksforGeeks - Summer Carnival Banner

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 :

    IDGroup_number
    11
    21
    31
    41
    52
    62
    72
    83
    93
    103
  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 :

    IDGroup_number
    11
    21
    32
    42
    53
    63
    74
    84
    95
    105
  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). 
    

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :