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.

Syntax

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

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.

SELECT
NTILE(2) OVER(ORDER BY price, name) AS NT,
price, name
FROM ebooks;

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
FROM ebooks;

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,
price, name
FROM ebooks;