Skip to main content

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.

Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

Basic Usage

denserank() order:Times group:Racer

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

Syntax and Arguments

denserank() order: order_col group: group_col

For more information on the orderand groupparameters, see Window Transform.

For more information on syntax standards, see Language Documentation Syntax Notes.

Examples

Tip

For additional examples, see Common Tasks.

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.

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

Transformation:

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

Transformation Name

Window

Parameter: Formulas

RANK()

Parameter: Group by

Runner

Parameter: Order by

Time

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:

Runner

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

In this section: