Important

New database features are no longer connected to engine versions; features are now enabled independently during scheduled update windows, and “major” and “minor” releases no longer exist in Helios. Please visit the release notes to view the latest features available in your database clusters.

FIRST_VALUE

Syntax

FIRST_VALUE ( expression ) OVER (
    [PARTITION BY (col | expr), ...]
    [ORDER BY (col | expr), ...]
    [frame_clause]
)

Arguments

expression: any expression. This may be a column name, the result of another function, or a math operation.

Return Type

The result type of expression

Examples

SQL

CREATE TABLE fv_example (a INT, b INT);

INSERT INTO fv_example VALUES (1, 2), (2, 2), (3, 3), (4, 3);

SQL

CREATE TABLE tick_table(TIMESTAMP DATETIME(6), symbol VARCHAR(5), comp_name VARCHAR (30), price NUMERIC(18,4));


INSERT INTO tick_table VALUES('2022-11-18 10:55:36.000000','STC','SomeTechCo', 100.00),
('2022-11-18 10:55:37.000000','STC','SomeTechCo', 102.00),
('2022-11-18 10:55:42.000000', 'STC', 'SomeTechCo', 105.00),
('2022-11-18 10:55:48.000000', 'STC', 'SomeTechCo', 101.00),
('2022-11-18 10:56:03.000000', 'STC', 'SomeTechCo', 99.00),
('2022-11-18 10:55:42.000000', 'AME', 'ACME', 198.00),
('2022-11-18 10:55:50.000000', 'AME', 'ACME', 208.00),
('2022-11-18 10:55:52.000000', 'AME', 'ACME', 210.00),
('2022-11-18 10:55:55.000000', 'AME', 'ACME', 211.00),
('2022-11-18 10:55:52.000000', 'OCO', 'OnCallCo', 21.00),
('2022-11-18 10:55:55.000000', 'OCO', 'OnCallCo', 22.00),
('2022-11-18 10:55:57.000000', 'OCO', 'OnCallCo', 21.00),
('2022-11-18 10:56:00.000000', 'OCO', 'OnCallCo', 23.00),
('2022-11-18 10:56:03.000000', 'OCO', 'OnCallCo', 24.00);

Window Function

SQL

SELECT a, FIRST_VALUE(a) OVER (ORDER BY a)
FROM fv_example;

+------+-----------------------------------+
| a    | FIRST_VALUE(a) OVER(ORDER BY (a)) |
+------+-----------------------------------+
|    1 |                                 1 |
|    2 |                                 1 |
|    3 |                                 1 |
|    4 |                                 1 |
+------+-----------------------------------+

SQL

SELECT TIMESTAMP, symbol, price,
       FIRST_VALUE(price) OVER (PARTITION BY symbol)
FROM tick_table
GROUP BY TIMESTAMP;

+----------------------------+--------+----------+------------------------------------------------+
| TIMESTAMP                  | symbol | price    | FIRST_VALUE(price) OVER (PARTITION BY symbol)  |
+----------------------------+--------+----------+------------------------------------------------+
| 2022-11-18 10:55:50.000000 | AME    | 208.0000 |                                       208.0000 |
| 2022-11-18 10:55:55.000000 | AME    | 211.0000 |                                       208.0000 |
| 2022-11-18 10:55:52.000000 | OCO    |  21.0000 |                                        21.0000 |
| 2022-11-18 10:55:57.000000 | OCO    |  21.0000 |                                        21.0000 |
| 2022-11-18 10:56:00.000000 | OCO    |  23.0000 |                                        21.0000 |
| 2022-11-18 10:56:03.000000 | OCO    |  24.0000 |                                        21.0000 |
| 2022-11-18 10:55:36.000000 | STC    | 100.0000 |                                       100.0000 |
| 2022-11-18 10:55:48.000000 | STC    | 101.0000 |                                       100.0000 |
| 2022-11-18 10:55:42.000000 | STC    | 105.0000 |                                       100.0000 |
| 2022-11-18 10:55:37.000000 | STC    | 102.0000 |                                       100.0000 |
+----------------------------+--------+----------+------------------------------------------------+

Window Frame Clause

SQL

SELECT a, FIRST_VALUE(a) OVER (
    ORDER BY a
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
)
FROM fv_example;

+------+---------------------------------------------------+
| a    | FIRST_VALUE(a) OVER(ORDER BY (a)                  |
|      | ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) |
+------+---------------------------------------------------+
|    1 |                                                 1 |
|    2 |                                                 2 |
|    3 |                                                 3 |
|    4 |                                                 4 |
+------+---------------------------------------------------+