Ntile
The Ntile function assigns the rows of a column to a given number of ranks. An approximately equal number of rows is given each Ntile rank. 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.
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 will be ranked 1, 2, 3 and 4 according to the size of the [Population 2010]
column. Since the direction is not specified it will default to ascending. Therefore, the lowest quartile of values in [Population 2010]
will be ranked 1 and the highest quartile of values will be ranked 4.
Ntile(4, [Population 2010], "desc")
Here, the direction argument is "desc"
, so the rank will be assigned descending. Therefore, the highest quartile of values in [Population 2010]
will be ranked 1 and the lowest quartile of values will be ranked 4.