- Categories:
Window Functions (Rank-related, Window Frame)
FIRST_VALUE¶
Returns the first value within an ordered group of values.
- See also:
Syntax¶
FIRST_VALUE( <expr> ) [ { IGNORE | RESPECT } NULLS ]
OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] [ <window_frame> ] )
For details about window_frame
syntax, see Window Frame Syntax and Usage.
Usage Notes¶
For compatibility with implementations of this function in other systems,
{ IGNORE | RESPECT } NULLS
can also be specified in the arguments for the function:FIRST_VALUE( <expr> [ { IGNORE | RESPECT } NULLS ] ) OVER ...
If
{ IGNORE | RESPECT } NULLS
is not specified, the default isRESPECT NULLS
(i.e. a NULL value will be returned if the expression contains a NULL value and it is the first value in the expression).FIRST_VALUE is a rank-related function so it requires a window to be specified, consisting of the following elements:
PARTITION BY <expr1>
subclause (optional).ORDER BY <expr2>
subclause (required) with an optionalwindow_frame
. For more details about additional supported ordering options (sort order, ordering of NULL values, etc.), see the ORDER BY query construct.
The optional
window_frame
(cumulative or sliding) specifies the subset of rows within the window for which the function is calculated. If nowindow_frame
is specified, the default is the entire window:ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Note that this deviates from the ANSI standard, which specifies the following default for window frames:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
For more details about window frames, including syntax and examples, see Window Frame Syntax and Usage.
The
ORDER BY
in theFIRST_VALUE
clause affects only the order of the rows in theFIRST_VALUE
clause; it does not control the order of the results of the entire query.
Examples¶
This shows a simple query using FIRST_VALUE()
. This query contains two ORDER BY
sub-clauses, one to
control the order of rows in each partition, and one to control the order of the output of the full query.
SELECT
column1,
column2,
FIRST_VALUE(column2) OVER (PARTITION BY column1 ORDER BY column2 NULLS LAST) AS column2_first
FROM VALUES
(1, 10), (1, 11), (1, null), (1, 12),
(2, 20), (2, 21), (2, 22)
ORDER BY column1, column2;
+---------+---------+---------------+
| COLUMN1 | COLUMN2 | COLUMN2_FIRST |
|---------+---------+---------------|
| 1 | 10 | 10 |
| 1 | 11 | 10 |
| 1 | 12 | 10 |
| 1 | NULL | 10 |
| 2 | 20 | 20 |
| 2 | 21 | 20 |
| 2 | 22 | 20 |
+---------+---------+---------------+
The next query contrasts the outputs of FIRST_VALUE
, NTH_VALUE
, and LAST_VALUE
. Note that:
The query creates a sliding window frame that is 3 rows wide, which contains:
The row that precedes the current row.
The current row.
The row that follows the current row.
The
2
in the callNTH_VALUE(i, 2)
specifies the second row in the window frame (which, in this case, is also the current row).When the current row is the very first row in the window frame, there is no preceding row to reference, so
FIRST_VALUE()
returns a NULL for that row.
SELECT
partition_col, order_col, i,
FIRST_VALUE(i) OVER (PARTITION BY partition_col ORDER BY order_col
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS FIRST_VAL,
NTH_VALUE(i, 2) OVER (PARTITION BY partition_col ORDER BY order_col
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS NTH_VAL,
LAST_VALUE(i) OVER (PARTITION BY partition_col ORDER BY order_col
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS LAST_VAL
FROM demo1
ORDER BY partition_col, i, order_col;
+---------------+-----------+---+-----------+---------+----------+
| PARTITION_COL | ORDER_COL | I | FIRST_VAL | NTH_VAL | LAST_VAL |
|---------------+-----------+---+-----------+---------+----------|
| 1 | 1 | 1 | NULL | 1 | 2 |
| 1 | 2 | 2 | 1 | 2 | 3 |
| 1 | 3 | 3 | 2 | 3 | 4 |
| 1 | 4 | 4 | 3 | 4 | 5 |
| 1 | 5 | 5 | 4 | 5 | 5 |
| 2 | 1 | 1 | NULL | 1 | 2 |
| 2 | 2 | 2 | 1 | 2 | 3 |
| 2 | 3 | 3 | 2 | 3 | 4 |
| 2 | 4 | 4 | 3 | 4 | 4 |
+---------------+-----------+---+-----------+---------+----------+