first_value aggregate function

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

Returns the first value of expr for a group of rows. This function is a synonym for first aggregate function.

Syntax

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

You can also invoke this function as a window function using the OVER clause.

Arguments

  • expr: An expression of any type.
  • ignoreNull: An optional BOOLEAN literal that defaults to false.
  • cond: An optional Boolean expression filtering the rows used for aggregation.
  • IGNORE NULLS or RESPECT NULLS: When you use IGNORE NULLS or set ignoreNull to true, the function ignores any expr value that is NULL. The default is RESPECT NULLS.

Returns

The result has the same type as expr.

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

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

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