Computes the rank of an ordered set of value within groups. Tie values are assigned the same rank, and the next ranking is incremented by the number of tie values.
Rank values start at
1and increment.Ranking order varies depending on the data type of the source data. For more information, see Sort Order.
You must use the
groupandorderparameters to define the groups of records and the ordering column to which this transform is applied.- This function works with the following transforms:
- This function assigns ranking values to match the total number of rows in a group. For fewer discrete ranking values when ties are present, see DENSERANK Function.
Basic Usage
window value:RANK() order:MySales group:Salesman
Output: Generates the new column, which contains the ranking of mySales, grouped by the Salesman column.
Syntax and Arguments
window value:RANK() order: order_col group: group_col
For more information on the order and group parameters, see Window Transform.
For more information on syntax standards, see Language Documentation Syntax Notes.
Examples
Tip: For additional examples, see Common Tasks.
Tip: For additional examples, see Common Tasks.
Example - Rank Functions
Source: The following dataset contains lap times for three racers in a four-lap race. Note that for some racers, there are tie values for lap times. Transform: You can apply the You can use the Results: After renaming the columns, you have the following output:RANK - Generates a ranked order of values, ranked within a group. DENSERANK - Generates a ranked order of values, ranked within a group.Runner Lap Time Dave 1 72.2 Dave 2 73.31 Dave 3 72.2 Dave 4 70.85 Mark 1 71.73 Mark 2 71.73 Mark 3 72.99 Mark 4 70.63 Tom 1 74.43 Tom 2 70.71 Tom 3 71.02 Tom 4 72.98 RANK() function to the Time column, grouped by individual runner:window value: RANK() group: Runner order: TimeDENSERANK() function on the same column, grouping by runner:window value: DENSERANK() group: Runner order: TimeRunner Lap Time Rank Rank-Dense Mark 4 70.63 1 1 Mark 1 71.73 2 2 Mark 2 71.73 2 2 Mark 3 72.99 4 3 Tom 2 70.71 1 1 Tom 3 71.02 2 2 Tom 4 72.98 3 3 Tom 1 74.43 4 4 Dave 4 70.85 1 1 Dave 1 72.2 2 2 Dave 3 72.2 2 2 Dave 2 73.31 4 3
This page has no comments.