Skip to main content

EXAMPLE - RANK Functions

This example demonstrates you to generate a ranked order of values.

Functions:

Item

Description

RANK 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 the number of tie values.

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.

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