Access data from a following row. This function returns the value of an expression from a row at a specified physical offset after the current row.
LEAD (expr[, offset]) OVER (
[ PARTITION BY partition_expression ]
[ ORDER BY order_list ]
)The LEAD function accesses data from a following row in the same result set without the need for a self-join. The specific row is determined by the offset (default 1), which specifies the number of rows ahead the current row to use. An offset of 0 is the current row.
- Window function to access a previous row
This page is licensed: CC BY-SA / Gnu FDL
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, LEAD(pk) OVER (ORDER BY pk) AS l,
LEAD(pk,1) OVER (ORDER BY pk) AS l1,
LEAD(pk,2) OVER (ORDER BY pk) AS l2,
LEAD(pk,0) OVER (ORDER BY pk) AS l0,
LEAD(pk,-1) OVER (ORDER BY pk) AS lm1,
LEAD(pk,-2) OVER (ORDER BY pk) AS lm2
FROM t1;
+----+------+------+------+------+------+------+
| pk | l | l1 | l2 | l0 | lm1 | lm2 |
+----+------+------+------+------+------+------+
| 1 | 2 | 2 | 3 | 1 | NULL | NULL |
| 2 | 3 | 3 | 4 | 2 | 1 | NULL |
| 3 | 4 | 4 | 5 | 3 | 2 | 1 |
| 4 | 5 | 5 | 6 | 4 | 3 | 2 |
| 5 | 6 | 6 | 7 | 5 | 4 | 3 |
| 6 | 7 | 7 | 8 | 6 | 5 | 4 |
| 7 | 8 | 8 | 9 | 7 | 6 | 5 |
| 8 | 9 | 9 | 10 | 8 | 7 | 6 |
| 9 | 10 | 10 | 11 | 9 | 8 | 7 |
| 10 | 11 | 11 | NULL | 10 | 9 | 8 |
| 11 | NULL | NULL | NULL | 11 | 10 | 9 |
+----+------+------+------+------+------+------+