What is NTILE() Function in SQL?

Programming Language
#1

NTILE() function 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.

Example :

Let us create a table named hello_demo :

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

Now,

SELECT * FROM hello_demo;

Output:


1 
2 
3 
4 
5 
6 
7 
8 
9 
10