In SQL Server, the NTILE() function allows you to distribute the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at 1. For each row, NTILE() returns the number of the group to which the row belongs.

You simply provide the number of groups you desire when calling the function.

Syntax

The syntax goes like this:

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

integer_expression is a positive integer expression that specifies the number of groups into which each partition must be divided. It can be of type int, or bigint.

<partition_by_clause> is optional. It divides the result set produced by the FROM clause into partitions to which the function is applied.

<order_by_clause> is required. It determines the order in which the NTILE() values are assigned to the rows in a partition. An integer cannot represent a column when the <order_by_clause> is used in a ranking function.

Example 1 – Basic Usage

Here’s a basic example showing how this function works:

SELECT 
  Player,
  Score,
  NTILE(4) OVER (ORDER BY Score DESC) 'NTILE'
FROM Scoreboard;

Result:

+----------+---------+---------+
| Player   | Score   | NTILE   |
|----------+---------+---------|
| Bart     | 2010    | 1       |
| Burns    | 1270    | 1       |
| Meg      | 1030    | 2       |
| Marge    | 990     | 2       |
| Lisa     | 710     | 3       |
| Ned      | 666     | 3       |
| Apu      | 350     | 4       |
| Homer    | 1       | 4       |
+----------+---------+---------+

In this case there are 8 results and the value I provide to NTILE() is 4, so the values are evenly spread across 4 groups.

Example 2 – Change the NTILE Value

Here’s what happens if I change the NTILE() value to 3.

SELECT 
  Player,
  Score,
  NTILE(3) OVER (ORDER BY Score DESC) 'NTILE'
FROM Scoreboard;

Result:

+----------+---------+---------+
| Player   | Score   | NTILE   |
|----------+---------+---------|
| Bart     | 2010    | 1       |
| Burns    | 1270    | 1       |
| Meg      | 1030    | 1       |
| Marge    | 990     | 2       |
| Lisa     | 710     | 2       |
| Ned      | 666     | 2       |
| Apu      | 350     | 3       |
| Homer    | 1       | 3       |
+----------+---------+---------+

The results are distributed across 3 groups. As you might expect, the last group ends up with only 2 rows (compared to 3 for the other groups).

Example 3 – Switch the Ordering

Switching the ordering between ascending and descending will usually result in the NTILE() values being applied to different rows.

SELECT 
  Player,
  Score,
  NTILE(4) OVER (ORDER BY Score DESC) 'NTILE Descending',
  NTILE(4) OVER (ORDER BY Score ASC) 'NTILE Ascending'
FROM Scoreboard
ORDER BY Score DESC;

Result:

+----------+---------+--------------------+-------------------+
| Player   | Score   | NTILE Descending   | NTILE Ascending   |
|----------+---------+--------------------+-------------------|
| Bart     | 2010    | 1                  | 4                 |
| Burns    | 1270    | 1                  | 4                 |
| Meg      | 1030    | 2                  | 3                 |
| Marge    | 990     | 2                  | 3                 |
| Lisa     | 710     | 3                  | 2                 |
| Ned      | 666     | 3                  | 2                 |
| Apu      | 350     | 4                  | 1                 |
| Homer    | 1       | 4                  | 1                 |
+----------+---------+--------------------+-------------------+

However, this will depend on how many rows are in the result set vs the number of NTILEs. Obviously, if the NTILE() value is 1, then there will be no difference.

SELECT 
  Player,
  Score,
  NTILE(1) OVER (ORDER BY Score DESC) 'NTILE Descending',
  NTILE(1) OVER (ORDER BY Score ASC) 'NTILE Ascending'
FROM Scoreboard
ORDER BY Score DESC;

Result:

+----------+---------+--------------------+-------------------+
| Player   | Score   | NTILE Descending   | NTILE Ascending   |
|----------+---------+--------------------+-------------------|
| Bart     | 2010    | 1                  | 1                 |
| Burns    | 1270    | 1                  | 1                 |
| Meg      | 1030    | 1                  | 1                 |
| Marge    | 990     | 1                  | 1                 |
| Lisa     | 710     | 1                  | 1                 |
| Ned      | 666     | 1                  | 1                 |
| Apu      | 350     | 1                  | 1                 |
| Homer    | 1       | 1                  | 1                 |
+----------+---------+--------------------+-------------------+

Same thing will happen if the result set contains just one row, regardless of the NTILE() value:

SELECT 
  Player,
  Score,
  NTILE(4) OVER (ORDER BY Score DESC) 'NTILE Descending',
  NTILE(4) OVER (ORDER BY Score ASC) 'NTILE Ascending'
FROM Scoreboard
WHERE Score > 2000
ORDER BY Score DESC;

Result:

+----------+---------+--------------------+-------------------+
| Player   | Score   | NTILE Descending   | NTILE Ascending   |
|----------+---------+--------------------+-------------------|
| Bart     | 2010    | 1                  | 1                 |
+----------+---------+--------------------+-------------------+

Example 4 – Partitions

You can use the PARTITION BY clause to divide the results into partitions. When you do this, NTILE() is applied against each partition.

Example:

SELECT 
  TeamName,
  Player,
  Score,
  NTILE(2) OVER (PARTITION BY TeamName ORDER BY Score ASC) 'NTILE'
FROM Scoreboard s
INNER JOIN Team t
ON t.TeamId = s.TeamId;

Result:

+------------+----------+---------+-------------------+
| TeamName   | Player   | Score   | NTILE             |
|------------+----------+---------+-------------------|
| Mongrels   | Apu      | 350     | 1                 |
| Mongrels   | Ned      | 666     | 1                 |
| Mongrels   | Meg      | 1030    | 2                 |
| Mongrels   | Burns    | 1270    | 2                 |
| Simpsons   | Homer    | 1       | 1                 |
| Simpsons   | Lisa     | 710     | 1                 |
| Simpsons   | Marge    | 990     | 2                 |
| Simpsons   | Bart     | 2010    | 2                 |
+------------+----------+---------+-------------------+