FIRST

Description

FIRST

is a window aggregate function that partitions rows into groups, orders rows by a field, and returns the value from the first row in the group.

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.

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 (first for this function) in each group.

Syntax

FIRST(input_field)OVER(
 PARTITION BY
partitioning_field[,partitioning_field]
 ORDER BY
ordering_field[ASC | DESC] [,ordering_field[ASC | DESC]]
 ROWS
start_window_boundary
  )
               
wherestart_window_boundarycan be:
               
 UNBOUNDED PRECEDING

Return Value

Returns a value of the same type as input_field.

Input Parameters

input_field: Required. The field on which to perform the aggregate function. You can use any
NUMERIC
field or a
CURRENCY
field.
OVER(): Required.
OVER
must be used within a
FIRST
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 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.
ROWS: Required. The
ROWS
clause defines the specific number of rows (relative to the current row) within the partition by specifying a window frame. You define the window frame by specifying start and end points within the partition, known as window boundaries. The window frame is the set of input rows in each partition, relative to the current row, over which to calculate the aggregate expression (first for this function). The window frame can include one, several, or all rows of the partition.
window_boundary: Required. The window boundaries define the start and end points of the window frame. Window boundaries are relative to the current row.

A
PRECEDING
clause defines a window boundary that is lower than the current row (the number of rows to include before the current row). If you specify only 1 window boundary, then Workday uses the current row as the last row in the window frame (the upper boundary). The
UNBOUNDED
keyword includes all rows in the direction specified.