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.
Ntile(ranks, [Column], direction)
- 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.
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.