LAST_VALUE(expr,[expr,...])
LAST_VALUE(expr) OVER ( [ PARTITION BY partition_expression ] [ ORDER BY order_list ] )
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.
Since MariaDB 10.2.2, LAST_VALUE can be used as a window function.
Returns NULL if no last value exists.
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 f_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 f_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 f_1f2f FROM t1; +------+------+------+--------+--------+--------+--------+--------+--------+ | i | f_1f | l_1f | f_1p1f | f_1p1f | f_2p1p | f_2p1p | f_1f2f | f_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 | +------+------+------+--------+--------+--------+--------+--------+--------+
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/last_value/