Skip to main content

last_value aggregate function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns the last value of expr for the group of rows. The function is a synonym for last aggregate function.

Syntax​

last_value(expr [, ignoreNull] ) [FILTER ( WHERE cond ) ] [ IGNORE NULLS | RESPECT NULLS ]

This function can also be invoked as a window function using the OVER clause.

Arguments​

  • expr: An expression of any type.
  • ignoreNull: An optional BOOLEAN literal defaulting to false.
  • cond: An optional boolean expression filtering the rows used for aggregation.
  • IGNORE NULLS or RESPECT NULLS: When IGNORE NULLS is used or ignoreNull is true, any expr value that is NULL is ignored. The default is RESPECT NULLS.

Returns​

The result type matches expr.

This function is non-deterministic.

Examples​

SQL
> SELECT last_value(col) FROM VALUES (10), (5), (20) AS tab(col);
 20

> SELECT last_value(col) FROM VALUES (10), (5), (NULL) AS tab(col);
 NULL

> SELECT last_value(col, true) FROM VALUES (10), (5), (NULL) AS tab(col);
 5

> SELECT last_value(col) IGNORE NULLS FROM VALUES (10), (5), (NULL) AS tab(col);
 5

> SELECT last_value(col) FILTER (WHERE col > 5) FROM VALUES (5), (20) AS tab(col);
 20
Last updated on