first_value aggregate function

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

Syntax

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

Arguments

  • expr: An expression of any type.

  • ignoreNull: An optional BOOLEAN literal defaulting to false. The default for ignoreNull is 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 has the same type as expr.

first_value is a synonym for first aggregate function.

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) IGNORE NULLS FROM VALUES (NULL), (5), (20) AS tab(col);
 5