Pular para o conteúdo principal
Última atualização em

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​

SQL
> 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
Nesta página