FIRST_
On this page
Returns an expression evaluated at the first row of the window frame.
If there are no rows in the frame, returns NULL.
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
CREATE TABLE fv_example (a INT, b INT);INSERT INTO fv_example VALUES (1, 2), (2, 2), (3, 3), (4, 3);
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
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 |
+------+-----------------------------------+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
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 |
+------+---------------------------------------------------+Last modified: February 16, 2023