Rank Data

Creates a new column of sequentially ranked data within the selected table.

Data is initially partitioned based on selected column(s). Partitioning data arranges all similar values together within the table. All equal values would be ranked the same value within each partition.

Arranging the elements in a partition is then possible based on secondary “order by” column(s).

The resulting ranked values can be output in ascending or descending order within each partitioned value set.

The values in the resulting column will depend on the type of ranking method selected.

Rank

Outputs a unique sequential value based on the partitioned column values. Rows with equal values will be ranked equally. A first place tie results in the next value being third place.

Dense Rank

Equal values will be ranked the same, but a first place tie results in the next value being second place.

Row Number

Outputs a unique sequential value based on the partitioned column values. Unlike Rank, Rows with equal values will be ranked sequentially, and values will not have ties.

Distribute into Groups (NTILE)

Creates a new column which groups the partitioned data (and optionally sorted data) into a desired number of groups. The resulting column is the group number for the row following the partitioning and sorting (ascending/descending) functions.

Distributing into 2 groups will result in a sorted table with a new column displaying 1 for the first half (0-49.9%) of all rows and the second half (50-100%) returning 2 .

Percent Rank

Returns the relative rank of the value within the partitioned data. The returned value in the new column is always between 0 and 1.

The returned column will use the formula:

$$Percent Rank = (Row Number -1)/ (Total Rows-1)$$

Row Number is the ranked value within the partition.

Total Rows is the total number of rows within the partition.