The SQL Server
NTILE function is a ranking function that allows you to assign ranks to rows in a result set.
This can be useful for creating partitions or groups of data.
The NTILE function takes two arguments: the number of partitions to create, and an expression to evaluate for each row.
The expression can be any valid expression, but is typically a column name.
The NTILE function will return a number between 1 and the number of partitions specified.
This number indicates which partition the row belongs to.
NTILE (integer_expression) OVER ( [
The NTILE function is useful when you wish to divide rows into equal portions across a partition. To use this ranking feature, you must specify how many categories to divide the rows into. In the event that the total number of rows is not evenly divided by the total number of groups, some of the groups will get an extra row. Groups having an additional row will be placed at the top of the list.
NTILE with OVER clause
In the example below, the NTILE function used together with the OVER clause divides the rows into two groups based on prices.
NTILE(2) OVER(ORDER BY price, name) AS NT,
NTILE with PARTITION BY clause
The following example shows how the NTILE function can be used with the PARTITION BY clause:
SELECT name, price,
NTILE(3) OVER (PARTITION BY price ORDER BY name) AS NT
NTILE with TOP
For example, the following query uses the NTILE function to divide the rows into four groups, then returns the first 5 e-books:
SELECT TOP 5
NTILE(4) OVER(ORDER BY price) AS NT,