{"id":1746,"date":"2018-09-08T18:56:03","date_gmt":"2018-09-09T01:56:03","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=1746"},"modified":"2025-01-20T09:15:05","modified_gmt":"2025-01-20T16:15:05","slug":"sql-ntile","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-window-functions\/sql-ntile\/","title":{"rendered":"SQL NTILE Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL NTILE()<\/code> function to break a result set into a specified number of buckets.<\/p>\n\n\n\n

An Overview of SQL NTILE() function #<\/a><\/h2>\n\n\n\n

The SQL NTILE()<\/code> is a window function<\/a> that allows you to break the result set into a specified number of approximately equal groups, or buckets. It assigns each group a bucket number starting from one. For each row in a group, the NTILE()<\/code> function assigns a bucket number representing the group to which the row belongs.<\/p>\n\n\n\n

The syntax of the NTILE()<\/code> function is as follows:<\/p>\n\n\n

NTILE(buckets) OVER ( \n\tPARTITION BY expr1, expr2,...\n\tORDER BY expr1 [ASC|DESC], expr2 ...\n)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Let’s examine the syntax in detail:<\/p>\n\n\n\n
buckets #<\/a><\/h3>\n\n\n\n
The number of buckets, which is a literal positive integer number or an expression that evaluates to a positive integer number.<\/p>\n\n\n\n
PARTITION BY #<\/a><\/h3>\n\n\n\n
The PARITITION BY<\/code> clause divides the result set returned from the FROM<\/code> clause into partitions to which the NTILE()<\/code> function is applied.<\/p>\n\n\n\n
ORDER BY #<\/a><\/h3>\n\n\n\n
The ORDER BY<\/code> clause specifies the order of rows in each partition to which the NTILE()<\/code> is applied.<\/p>\n\n\n\n
Notice that if the number of rows is not divisible by buckets<\/code>, the NTILE()<\/code> function results in groups of two sizes with the difference by one. The larger groups always come before the smaller group in the order specified by the ORDER BY<\/code> clause.<\/p>\n\n\n\n
In case the total of rows is divisible by buckets<\/code>, the rows are divided evenly among groups.<\/p>\n\n\n\n
The following statement creates a new table named t<\/code> that stores 10 integers from one to ten:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> t (\n\tcol<\/span> INT<\/span> NOT<\/span> NULL<\/span>\n);\n\t\nINSERT<\/span> INTO<\/span> t(col<\/span>) \nVALUES<\/span>(1<\/span>),(2<\/span>),(3<\/span>),(4<\/span>),(5<\/span>),(6<\/span>),(7<\/span>),(8<\/span>),(9<\/span>),(10<\/span>);\n\t\n\t\nSELECT<\/span> * FROM<\/span> t;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you use the NTILE()<\/code> function to divide ten rows into three groups, you will have the first group with four rows and other two groups with three rows.<\/p>\n\n\n
SELECT<\/span> \n\tcol<\/span>, \n\tNTILE (3<\/span>) OVER<\/span> (\n\t\tORDER<\/span> BY<\/span> col<\/span>\n\t) buckets\nFROM<\/span> \n\tt;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following shows the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
As clearly shown in the output, the first group has four rows while the other groups have three rows.<\/p>\n\n\n\n
The following statement uses two instead of three buckets:<\/p>\n\n\n
SELECT<\/span> \n\tcol<\/span>, \n\tNTILE (2<\/span>) OVER<\/span> (\n\t\tORDER<\/span> BY<\/span> col<\/span>\n\t) buckets\nFROM<\/span> \n\tt;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
Now, we have two groups which have the same number of rows.<\/p>\n\n\n\n
SQL NTILE() function examples #<\/a><\/h2>\n\n\n\n
See the following employees<\/code> table from the sample database:<\/p>\n\n\n\n
Using SQL NTILE() function over the result set example #<\/a><\/h3>\n\n\n\n
The following statement uses the NTILE()<\/code> function to divide the employees into five buckets based on their salaries:<\/p>\n\n\n
SELECT<\/span>\n\tfirst_name, \n\tlast_name, \n\tsalary,\n\tNTILE(5<\/span>) OVER<\/span> (\n\t\tORDER<\/span> BY<\/span> salary DESC<\/span>\n\t) salary_group\nFROM<\/span> \n\temployees;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
Using SQL NTILE() function over partition example #<\/a><\/h3>\n\n\n\n
The following statement breaks the employees in each department into two groups:<\/p>\n\n\n
SELECT<\/span>\n\tfirst_name, \n\tlast_name, \n\tdepartment_name,\n\tsalary,\n\tNTILE(2<\/span>) OVER<\/span> (\n\t\tPARTITION<\/span> BY<\/span> department_name\n\t\tORDER<\/span> BY<\/span> salary\n\t) salary_group\nFROM<\/span> \n\temployees e\nINNER<\/span> JOIN<\/span> departments d\n\tON<\/span> d.department_id = e.department_id;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following shows the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example:<\/p>\n\n\n\n