{ FIRST_VALUE | LAST_VALUE } (
value_expression [ { IGNORE | RESPECT } NULLS ]
) window
Syntax Elements
- value_expression
- A column expression.
- FIRST_VALUE and LAST_VALUE use the default data type of value_expression.
- Larger numeric values are supported by casting them to a higher data type.
- The expression cannot contain any ordered analytical or aggregate functions.
- IGNORE NULLS
- Keyword that specifies not to return NULL.
- IGNORE NULLS (with FIRST_VALUE) = returns the first non-null value in the set, or NULL if all values are NULL.
- If IGNORE NULLS (with LAST_VALUE) = returns the last non-null value in the set, or NULL if all values are NULL.
- RESPECT NULLS
- Optional keyword that specifies whether to return NULL.
- RESPECT NULLS (with FIRST_VALUE) = returns the first value, whether or not it is null.
- RESPECT NULLS (with LAST_VALUE) = returns the last value, whether or not it is null.
- If all values are null, NULL is returned.
- window
- A group, cumulative, or moving computation.
- See The Window Specification.
- In presence of ties in the sort key of the Window Aggregate Function syntax, FIRST_VALUE and LAST_VALUE are non-deterministic. They return value_expression from any one of the rows with tied order by value.
If the ROWS phrase is omitted and there is an ORDER BY phrase, the default ROWS is UNBOUNDED PRECEDING AND CURRENT ROW.
If the ROWS phrase is omitted and there is no ORDER BY phrase, the default ROWS is UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.