LAST

Description

LAST

is a window aggregate function that partitions rows into groups, orders rows by a field, and returns the value from the last 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 (last for this function) in each group.

Syntax

LAST(input_field) OVER(
   PARTITION BY
partitioning_field [, partitioning_field]
   ORDER BY
ordering_field [ASC | DESC] [, ordering_field [ASC | DESC]]
   ROWS
BETWEEN
start_window_boundary
AND
end_window_boundary
   )
                
where start_window_boundary can be:

  CURRENT ROW
            
and where end_window_boundary can be:
                
  UNBOUNDED FOLLOWING

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
LAST
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 (last 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
FOLLOWING
clause defines a window boundary that is after the current row (the number of rows to include after the current row). Workday uses the current row as the first row in the window frame (the lower boundary). The
UNBOUNDED
keyword includes all rows in the direction specified.