NTILE Function in SQL Server

NTILE is one of the ranking functions in SQL Server. NTILE function is used in scenarios where we have a requirement to divide the result set into some quarters, and with each row, we want to get that quarter’s number. We might think of real-life situations such as suppose we have to get email addresses for campaigning from a database. And we also want that these returned emails are divided into four groups.

The NTILE authorizes users to separate a series set into an informative and detailed number of moderately comparable groups, or receptacles, rows endorsing. If the number of rows in the cluster is more undersized than the number of containers specified, then the number of containers will be reduced. It is a window function that circulates rows of a ranked division into a pre-specified number of approximately equivalent levels. It allocates each cluster a number_expression ranging from 1. It also allots a number_expression per row in a group.

This function comes with three main components. These are ORDER BY, PARTITION BY, and BUCKETS.

The buckets include the number of rows separated. The buckets can be an indication or subquery that assesses a positive integer. It cannot be a window function. The PARTITION BY clause allocates rows of an outcome set into sections to which the NTILE() function is used. The ORDER BY clause specifies the analytical hierarchy of rows in each section to which the NTILE() is used.

If the number of rows is not separable by the buckets, the NTILE() function produces clusters of two dimensions with the discrepancy by one. The more big groups always appear before the smaller group in the order described by the ORDER BY in the OVER() clause. On the other hand, if the sum of rows is separable by the buckets, it splits evenly.

The important thing to remember here is that If the number of rows in a section is not separable by integer_expression, then this will push clusters of two dimensions that vary by one associate. As mentioned earlier, the larger groups appear before more miniature groups in the order determined by the OVER clause. In other words, SQL NTILE() is a window function that is mainly utilized for breaking a window or a section in a particular number of buckets.

The syntax for NTILE is as follows.

NTILE (no_of_quarter) OVER ( [  ] < order_by_clause > )

Let’s say we want to get some products in inventory with their quantity. And we also want these results set to be divided into four quarters. Its query will be as below.

How does NTILE divide the result set?

If total rows are not divisible by the number of quarters specified, then we have different sized groups. In the above example, we have a total of 10 rows and 4 divisions. As a result, the first two divisions have 3 rows each and the last two divisions have 2 rows each. Now if we re-run these queries by providing two quarters, then we have evenly distributed groups with 5 rows each.

For more granule partition within each group, we can use NTILE with PARTITION BY statement.

   

About the Author

ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.