Skip to main content

FIRST_VALUE function

Returns the value of the value_expression for the first row in the current window frame.

Syntax

FIRST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])
            OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  ORDER BY expression [ { ASC | DESC }  ] [, ...]
  [ window_frame_clause ]

Arguments

  • value_expression can be any data type that an expression can return.

Returns

Same type as value_expression

Examples

> SELECT boolean_val, row_id,
         FIRST_VALUE(row_id) OVER (PARTITION BY boolean_val ORDER BY row_id DESC
             ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING),
         FIRST_VALUE(row_id) OVER (PARTITION BY boolean_val ORDER BY row_id DESC
             ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
  FROM TestTable;

----
<BOOLEAN,BIGINT,BIGINT,BIGINT>
NULL, 1, NULL, NULL
false, 9, NULL, 7
false, 7, 9, 5
false, 5, 9, 3
false, 3, 7, NULL
true, 10, NULL, 8
true, 8, 10, 6
true, 6, 10, 4
true, 4, 8, 2
true, 2, 6, NULL