LAST_VALUE

Syntax

LAST_VALUE(expr,[expr,...])

LAST_VALUE(expr) OVER (  [ PARTITION BY partition_expression ]  [ ORDER BY order_list ] )

Description

LAST_VALUE() evaluates all expressions and returns the last. This is useful together with setting user variables to a value with @var:=expr, for example when you want to get data of rows updated/deleted without having to do two queries against the table.

LAST_VALUE can be used as a window function.

Returns NULL if no last value exists.

Examples

CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES(1,10),(2,20); DELETE FROM t1 WHERE a=1 AND last_value(@a:=a,@b:=b,1); SELECT @a,@b; +------+------+ | @a | @b | +------+------+ | 1 | 10 | +------+------+

As a window function:

CREATE TABLE t1 (  pk int primary key,  a int,  b int,  c char(10),  d decimal(10, 3),  e real );  INSERT INTO t1 VALUES ( 1, 0, 1, 'one', 0.1, 0.001), ( 2, 0, 2, 'two', 0.2, 0.002), ( 3, 0, 3, 'three', 0.3, 0.003), ( 4, 1, 2, 'three', 0.4, 0.004), ( 5, 1, 1, 'two', 0.5, 0.005), ( 6, 1, 1, 'one', 0.6, 0.006), ( 7, 2, NULL, 'n_one', 0.5, 0.007), ( 8, 2, 1, 'n_two', NULL, 0.008), ( 9, 2, 2, NULL, 0.7, 0.009), (10, 2, 0, 'n_four', 0.8, 0.010), (11, 2, 10, NULL, 0.9, NULL);  SELECT pk, FIRST_VALUE(pk) OVER (ORDER BY pk) AS first_asc,  LAST_VALUE(pk) OVER (ORDER BY pk) AS last_asc,  FIRST_VALUE(pk) OVER (ORDER BY pk DESC) AS first_desc,  LAST_VALUE(pk) OVER (ORDER BY pk DESC) AS last_desc FROM t1 ORDER BY pk DESC;  +----+-----------+----------+------------+-----------+ | pk | first_asc | last_asc | first_desc | last_desc | +----+-----------+----------+------------+-----------+ | 11 | 1 | 11 | 11 | 11 | | 10 | 1 | 10 | 11 | 10 | | 9 | 1 | 9 | 11 | 9 | | 8 | 1 | 8 | 11 | 8 | | 7 | 1 | 7 | 11 | 7 | | 6 | 1 | 6 | 11 | 6 | | 5 | 1 | 5 | 11 | 5 | | 4 | 1 | 4 | 11 | 4 | | 3 | 1 | 3 | 11 | 3 | | 2 | 1 | 2 | 11 | 2 | | 1 | 1 | 1 | 11 | 1 | +----+-----------+----------+------------+-----------+

CREATE OR REPLACE TABLE t1 (i int); INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);  SELECT i,  FIRST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW and 1 FOLLOWING) AS f_1f,  LAST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW and 1 FOLLOWING) AS l_1f,  FIRST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS f_1p1f,  LAST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS l_1p1f,  FIRST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS f_2p1p,  LAST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS l_2p1p,  FIRST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS f_1f2f,  LAST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS l_1f2f FROM t1;  +------+------+------+--------+--------+--------+--------+--------+--------+ | i | f_1f | l_1f | f_1p1f | l_1p1f | f_2p1p | l_2p1p | f_1f2f | l_1f2f | +------+------+------+--------+--------+--------+--------+--------+--------+ | 1 | 1 | 2 | 1 | 2 | NULL | NULL | 2 | 3 | | 2 | 2 | 3 | 1 | 3 | 1 | 1 | 3 | 4 | | 3 | 3 | 4 | 2 | 4 | 1 | 2 | 4 | 5 | | 4 | 4 | 5 | 3 | 5 | 2 | 3 | 5 | 6 | | 5 | 5 | 6 | 4 | 6 | 3 | 4 | 6 | 7 | | 6 | 6 | 7 | 5 | 7 | 4 | 5 | 7 | 8 | | 7 | 7 | 8 | 6 | 8 | 5 | 6 | 8 | 9 | | 8 | 8 | 9 | 7 | 9 | 6 | 7 | 9 | 10 | | 9 | 9 | 10 | 8 | 10 | 7 | 8 | 10 | 10 | | 10 | 10 | 10 | 9 | 10 | 8 | 9 | NULL | NULL | +------+------+------+--------+--------+--------+--------+--------+--------+

See Also

• Setting a variable to a value

_{This page is licensed: CC BY-SA / Gnu FDL}