ROW_NUMBER

Description

ROW_NUMBER

is a window aggregate function that partitions rows into groups, orders rows by a field, and assigns a unique, sequential number to each row in a group, starting at 1 for the first row in each group.

ROW_NUMBER

always assigns a unique value to each row in a group. You might want to use

ROW_NUMBER

to create a unique ID for each row in your dataset.

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 assigned a sequential number.

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 (row numbering for this function) in each group. The numbered rows in each group start at 1.

Syntax

ROW_NUMBER()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
ROW_NUMBER
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 assign a unique ID to the sales of each employee in descending order, so the highest sales is given the ranking of 1. Use this expression in the Sales Num by Employee
field:

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

You get these results:

Employee	Sales Date	Sales	Sales Num by Employee
Goh	12/31/2018	140	1
Goh	10/31/2018	140	2
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 assign a unique ID to the sales for each date in descending order, so the highest sales is given the ranking of 1. Use this expression in the Sales Num by Date
field:

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

You get these results:

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

You can also use

ROW_NUMBER

to determine the latest version of every row in a dataset that contains multiple rows per ID. In this scenario, the dataset requires a date field that represents when the information in that row became current. If you're familiar with data warehousing concepts, this is a type 2 slowly changing dimension table. You have a dataset with these rows and fields.

ID	Name	Region	Effective Date
G1	Gorman	West	01/01/2015
H1	Harris	West	01/01/2015
G1	Gorman	East	09/01/2016
H1	Harris	East	01/03/2017
H1	Harris	National	01/20/2021

To assign the value of 1 to the latest version of each ID, use this expression in the Latest Version
field:

ROW_NUMBER() OVER(
 PARTITION BY [ID]
 ORDER BY [Effective Date] DESC)

You get these results:

ID	Name	Region	Effective Date	Latest Version
G1	Gorman	West	01/01/2015	2
H1	Harris	West	01/01/2015	3
G1	Gorman	East	09/01/2016	1
H1	Harris	East	01/03/2017	2
H1	Harris	National	01/20/2021	1

You can filter on Latest Version
using a Filter Stage in order to return only the latest row for each ID.