RANK

Description

RANK

is a window aggregate function used to assign a ranking number to each row in a group. If multiple rows have the same ranking value (there's a tie), then Workday assigns the same rank value to the tied rows and skips the subsequent rank position.

The

PARTITION BY

clause determines which fields to use to partition a set of input rows into groups.

The

ORDER BY

clause determines how to order the rows in the partition before they're ranked.

Workday separates the input rows into groups according to the partitioning fields, orders the rows according to the ordering fields, and then computes the aggregate expression (rank for this function) in each group. The ranked rows in each group start at 1.

Syntax

RANK() OVER(
   PARTITION BY
partitioning_field [, partitioning_field]
   ORDER BY
ordering_field [ASC | DESC] [, ordering_field [ASC | DESC]]
   )

Return Value

Returns a value of type

Integer

.

Input Parameters

OVER(): Required.
OVER
must be used within a
RANK
expression.
PARTITION BY partitioning_field: Required. Use the
PARTITION BY
clause to specify 1 or more fields to use to partition a group of input rows. You can specify any field type except Currency. Example: You specify the Month field as the partitioning field, so Workday groups together into a single partition all records that have the same value for Month.
ORDER BY ordering_field: Required. Use the
ORDER BY
clause to specify how to order the input rows in the partition using the values in the specified field within each partition. You can specify any field type except Currency.
You can use the
DESC
or
ASC
keywords to sort in descending order (high to low values, NULLs are last) or ascending order (low to high values, NULLs are first) for each ordering field. If you don't specify a sort order for an ordering field, Workday automatically sorts rows in ascending order.

Examples

Example: You have a dataset with these rows and fields.

Employee	Sales Date	Sales
Goh	12/31/2018	140
Goh	11/30/2018	60
Goh	10/31/2018	140
Freeman	12/31/2018	160
Freeman	11/30/2018	60
Freeman	10/31/2018	110
Smith	12/31/2018	140
Smith	11/30/2018	60
Smith	10/31/2018	120

You can rank the sales for each employee in descending order, so the highest sales is given the ranking of 1. Use this expression in the Rank Sales by Employee
field:

RANK() OVER(
  PARTITION BY [Employee]
  ORDER BY [Sales] DESC)

You get these results:

Employee	Sales Date	Sales	Rank Sales by Employee
Goh	12/31/2018	140	1
Goh	10/31/2018	140	1
Goh	11/30/2018	60	3
Freeman	12/31/2018	160	1
Freeman	10/31/2018	110	2
Freeman	11/30/2018	60	3
Smith	12/31/2018	140	1
Smith	10/31/2018	120	2
Smith	11/30/2018	60	3

You can also rank the sales for each date in descending order, so the highest sales is given the ranking of 1. Use this expression in the Rank Sales by Date
field:

RANK() OVER(
  PARTITION BY [Sales Date]
  ORDER BY [Sales] DESC)

You get these results:

Employee	Sales Date	Sales	Rank Sales by Date
Freeman	12/31/2018	160	1
Goh	12/31/2018	140	2
Smith	12/31/2018	140	2
Goh	11/30/2018	60	1
Freeman	11/30/2018	60	1
Smith	11/30/2018	60	1
Goh	10/31/2018	140	1
Smith	10/31/2018	120	2
Freeman	10/31/2018	110	3

Notice that tied values are given the same rank number and the following rank position is skipped.