IBM PureData System for Analytics, Version 7.1

First and last family syntax

The syntax for the first and last family is as follows:

{first_value | last_value}
({<column reference> | <value expression>} [IGNORE NULLS] ) OVER 
([partition by <value_expression>[,...]]
ORDER BY{<value_expression> [asc | desc] [nulls {first | last}]} 
[,...]] [ rows | range
{{ current row | unbounded preceding | <literal value> preceding} | 
between 
{current row | unbounded preceding | <literal value> preceding}
and {current row | unbounded following | <literal value> following}}])

first_value: Returns the first value in an ordered set of values. If the first value in the set is null, the function returns NULL unless you specify IGNORE NULLS. If you specify IGNORE NULLS, FIRST_VALUE returns the first non-null value in the set, or NULL if all values are null.
last_value: Returns the last value in an ordered set of values. If the last value in the set is null, the function returns NULL unless you specify IGNORE NULLS. If you specify IGNORE NULLS, LAST_VALUE returns the last non-null value in the set, or NULL if all values are null.