FIRST_VALUE
You are viewing an old version of this article. View
the current version here.
Contents
Syntax
FIRST_VALUE(expr) OVER ( [ PARTITION BY partition_expression ] [ ORDER BY order_list ] )
Description
FIRST_VALUE
returns the first result from an ordered set, or NULL if no such result exists.
Examples
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
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.