FIRST_VALUE/LAST_VALUE Function Syntax | Teradata Vantage - 17.10 - FIRST_VALUE/LAST_VALUE Function Syntax - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1145-171K
Language
English (United States)
{ 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.