DENSERANK Function

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 1.
  • Rank values start at 1 and increment.

  • Ranking order varies depending on the data type of the source data. For more information, see Sort Order.

  • You must use the group and order parameters to define the groups of records and the order of those records to which this function is applied.

  • This function works with the following transforms:
  • This function assigns ranking of the next value of a set of ties as a single increment more. For more discrete ranking, see RANK Function.

Basic Usage

denserank() order:Times group:Racer

Output: Returns the dense ranking of Times values, grouped by the Racer column.

Syntax

denserank() 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

Example - Rank Functions

This example demonstrates the following two functions:

  • RANK - Generates a ranked order of values, ranked within a group.
    • If there are three tie values in a group, the next ranking is three more than the tie values.
    • See RANK Function.
  • DENSERANK - Generates a ranked order of values, ranked within a group.
    • If there are three tie values in a group, the next ranking is one more than the tie values.
    • See DENSERANK Function.

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.

RunnerLapTime
Dave172.2
Dave273.31
Dave372.2
Dave470.85
Mark171.73
Mark271.73
Mark372.99
Mark470.63
Tom174.43
Tom270.71
Tom371.02
Tom472.98


Transformation:

You can apply the RANK() function to the Time column, grouped by individual runner:

Transformation Name Window
Parameter: Formulas RANK()
Parameter: Group by Time
Parameter: Order by Runner

You can use the DENSERANK() function on the same column, grouping by runner:

Transformation Name Window
Parameter: Formulas DENSERANK()
Parameter: Group by Runner
Parameter: Order by Time

Results:

After renaming the columns, you have the following output:

RunnerLapTimeRankRank-Dense
Mark470.6311
Mark171.7322
Mark271.7322
Mark372.9943
Tom270.7111
Tom371.0222
Tom472.9833
Tom174.4344
Dave470.8511
Dave172.222
Dave372.222
Dave273.3143