FIRST_VALUE

​

Syntax

​

Aggregate

first_value(value) [IGNORE NULLS | RESPECT NULLS]

​

Window

first_value(value) [IGNORE NULLS | RESPECT NULLS] OVER ([PARTITION BY key] ORDER BY key [ROWS frame])

​

Arguments

​

Aggregate and window

​

Returns

• Type: same SQL type as value
• NULL behavior: RESPECT NULLS is default. IGNORE NULLS selects a non-NULL boundary value; all-NULL input yields NULL.
• Empty input: Returns NULL on empty input/frame.

​

Properties

​

Usage notes

• Grouped input order is unspecified; use a unique ordering key for stable window results.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• Query tools may report a DECIMAL result as DOUBLE or a TIME result as INTEGER. The returned value still keeps its decimal precision and scale, or its time type. A reported type does not convert the value.
• Supports DISTINCT: Adding DISTINCT does not remove duplicate input values.

​

Examples

​

Basic example

WITH t(id,"value") AS (VALUES (1,10),(2,5),(3,20)) SELECT first_value("value") OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS result FROM t ORDER BY id LIMIT 1;

​

All rows and running results, with NULL handling

WITH sample(id, v) AS (VALUES (1, CAST(NULL AS INTEGER)), (2, 10), (3, 5))
SELECT id,
  first_value(v) RESPECT NULLS OVER (ORDER BY id ASC NULLS LAST
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS whole_partition,
  first_value(v) IGNORE NULLS OVER (ORDER BY id ASC NULLS LAST
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_non_null
FROM sample ORDER BY id ASC NULLS LAST;

​

Limitations

• RANGE frames with a numeric or time offset are not supported.
• In grouped calls, DISTINCT does not remove duplicate input values. Selection can still vary when rows are tied or their order changes.
• The optional Boolean shorthand on FIRST is outside these calling forms; use the explicit one-argument function and NULL-treatment clause.