first_value aggregate function (Databricks SQL)

Returns the first value of expr for a group of rows.

Syntax

first_value(expr[, ignoreNull]) [FILTER ( WHERE cond ) ]

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.

Returns

The result has the same type as expr.

If ignoreNull is true the function returns only non-null values unless the entire group of values is NULL. The default for ignoreNull is false.

first_value is a synonym for first aggregate function (Databricks SQL).

This function is non-deterministic.

Examples

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