LAST_VALUE
Returns an expression evaluated at the last row of the window frame.
If there are no rows in the frame, returns NULL
.
For an overview of window functions in SingleStoreDB, see Window Functions Guide.
Syntax
LAST_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 lv_example (a INT, b INT); INSERT INTO lv_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, LAST_VALUE(a) OVER(ORDER BY (a)) FROM lv_example; **** +------+----------------------------------+ | a | LAST_VALUE(a) OVER(ORDER BY (a)) | +------+----------------------------------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +------+----------------------------------+
Window Frame Clause
SELECT a, LAST_VALUE(a) OVER(ORDER BY (a) ROWS between CURRENT ROW and UNBOUNDED FOLLOWING) FROM lv_example; **** +------+---------------------------------------------------+ | a | LAST_VALUE(a) OVER(ORDER BY (a) | | | ROWS between CURRENT ROW and UNBOUNDED FOLLOWING) | +------+---------------------------------------------------+ | 1 | 4 | | 2 | 4 | | 3 | 4 | | 4 | 4 | +------+---------------------------------------------------+
SELECT TIMESTAMP, symbol, price, LAST_VALUE(price) OVER ( PARTITION BY symbol) FROM tick_table GROUP BY TIMESTAMP; **** +----------------------------+--------+----------+------------------------+ | TIMESTAMP | symbol | price | LAST_VALUE(price) OVER | | | | | (PARTITION BY symbol) | +----------------------------+--------+----------+------------------------+ | 2022-11-18 10:55:55.000000 | AME | 211.0000 | 210.0000 | | 2022-11-18 10:55:50.000000 | AME | 208.0000 | 210.0000 | | 2022-11-18 10:55:42.000000 | AME | 198.0000 | 210.0000 | | 2022-11-18 10:55:52.000000 | AME | 210.0000 | 210.0000 | | 2022-11-18 10:55:57.000000 | OCO | 21.0000 | 23.0000 | | 2022-11-18 10:56:00.000000 | OCO | 23.0000 | 23.0000 | | 2022-11-18 10:55:36.000000 | STC | 100.0000 | 99.0000 | | 2022-11-18 10:55:37.000000 | STC | 102.0000 | 99.0000 | | 2022-11-18 10:55:48.000000 | STC | 101.0000 | 99.0000 | | 2022-11-18 10:56:03.000000 | STC | 99.0000 | 99.0000 | +----------------------------+--------+----------+------------------------+