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

> 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