Window functions

Ntile

View as Markdown

The Ntile function assigns the rows of a column to a given number of ranks. Each rank receives an approximately equal number of rows. The ranks are assigned in order.

Syntax

Ntile(ranks, [Column], direction)

Function Arguments:

  • ranks (required) - The number of ranks to assign. Must be an integer greater than 0.
  • [Column] (required) The column used to rank the table.
  • direction (optional) The direction to sort the input column. Enter "asc" to sort ascending and "desc" to sort descending. The default sort is ascending.

Notes

The Ntile function includes null values in the rank. Null values are ranked last, after all non-null values when sorted ascending, and before all non-null values when sorted descending.

Example

Ntile(4, [Population 2010])

A table contains the population of different counties in 2010. The table is grouped by a [State] column. Using the Ntile function, an equal number of rows are ranked 1, 2, 3, and 4 according to the size of the [Population 2010] column. Because the direction is not specified, the rank is assigned in the default ascending order. As a result, the lowest quartile of values in [Population 2010] are ranked 1 and the highest quartile of values, including null values, are ranked 4.

Table grouped by state showing county, Population 2010, and NTile columns, with counties ranked 1 through 4 in ascending order of population within each state.

Ntile(4, [Population 2010], "desc")

In this example, the direction argument is "desc", so the rank is assigned in descending order. As a result, the highest quartile of values in [Population 2010] are ranked 1 and the lowest quartile of values are ranked 4.

Table grouped by state showing county, Population 2010, and NTile columns, with counties ranked 1 through 4 in descending order of population within each state.