Categories: Window function syntax and usage

FIRST_VALUE

Returns the first value within an ordered group of values. For deterministic results over a full partition, specify ORDER BY and a frame such as ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Syntax

FIRST_VALUE(expr) [ { IGNORE | RESPECT } NULLS ]
  OVER (
    [PARTITION BY part]
    [ORDER BY ord [ASC | DESC] [window_frame]]
  )

For detailed window_frame syntax, see Window function syntax and usage.

Arguments

expr: The expression that determines the return value.

---

part (optional): Expression(s) to partition by. You can specify a single expression or a comma-separated list of expressions. For example:

PARTITION BY column_1, column_2

---

ord (recommended): Expression(s) to order within each partition. You can specify a single expression or a comma-separated list of expressions. For example:

ORDER BY column_3, column_4

---

window_frame: Subset of rows within the window. See Window function syntax and usage.

Usage Notes

• Without ORDER BY, “first” is implementation-dependent.
• Default ordered frame is RANGE … CURRENT ROW; use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to evaluate across the entire partition.
• IGNORE NULLS skips NULLs; RESPECT NULLS (default) includes them.

Examples

This example shows a simple query that uses the FIRST_VALUE function. This query contains two ORDER BY clauses: one to control the order of rows in each partition, and one to sort the output of the full query.

-- First value per group across the whole partition
SELECT column1,
       column2,
       FIRST_VALUE(column2) OVER (PARTITION BY column1 ORDER BY column2 NULLS LAST) AS column2_first
  FROM  (select 1 as column1, 10 as column2
         union all
         select 1 as column1, 11 as column2
         union all
         select 1 as column1, NULL as column2
         union all
         select 1 as column1, 12 as column2
         union all
         select 2 as column1, 20 as column2
         union all
         select 2 as column1, 21 as column2
         union all
         select 2 as column1, 22 as column2)
  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 following example returns the results of three related functions: FIRST_VALUE, NTH_VALUE, and LAST_VALUE.

The query creates a sliding window frame that is three 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 call NTH_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.

Frame boundaries sometimes extend beyond the rows in a partition, but non-existent rows are not included in window function calculations. For example, when the current row is the very first row in the partition and the window frame is ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING, there is no preceding row to reference, so the FIRST_VALUE function returns the value of the first row in the partition.

The results never match for all three functions, given the data in the table. These functions select the first, last, or nth value for each row in the frame, and the selection of values applies separately to each partition.

Run the following query:

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 (
        SELECT 1 AS i, 1 AS partition_col, 1 AS order_col UNION ALL
        SELECT 2 AS i, 1 AS partition_col, 2 AS order_col UNION ALL
        SELECT 3 AS i, 1 AS partition_col, 3 AS order_col UNION ALL
        SELECT 4 AS i, 1 AS partition_col, 4 AS order_col UNION ALL
        SELECT 5 AS i, 1 AS partition_col, 5 AS order_col UNION ALL
        SELECT 1 AS i, 2 AS partition_col, 1 AS order_col UNION ALL
        SELECT 2 AS i, 2 AS partition_col, 2 AS order_col UNION ALL
        SELECT 3 AS i, 2 AS partition_col, 3 AS order_col UNION ALL
        SELECT 4 AS i, 2 AS partition_col, 4 AS order_col
  ) 
  ORDER BY partition_col, i, order_col;

+---------------+-----------+---+-----------+---------+----------+
| PARTITION_COL | ORDER_COL | I | FIRST_VAL | NTH_VAL | LAST_VAL |
|---------------+-----------+---+-----------+---------+----------|
|             1 |         1 | 1 |         1 |       2 |        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 |         1 |       2 |        2 |
|             2 |         2 | 2 |         1 |       2 |        3 |
|             2 |         3 | 3 |         2 |       3 |        4 |
|             2 |         4 | 4 |         3 |       4 |        4 |
+---------------+-----------+---+-----------+---------+----------+