LEAD
On this page
Returns an expression evaluated at some offset after the current row.
If there is no such row returns NULL instead.
Syntax
LEAD ( expression [, offset]) OVER (
[PARTITION BY (col | expr), ...]
[ORDER BY (col | expr), ...]
)Arguments
-
expression: any expression.
This may be a column name, the result of another function, or a math operation -
offset: A constant integer that specifies the number of rows after the current row to evaluate the expression.
Defaults to 1 if not specified
Return Type
The result of expression
Examples
CREATE TABLE lead_example (a int, b int);INSERT INTO lead_example VALUES(1,2),(2,2),(3,3),(4,3);
SELECT a, LEAD(a) OVER(ORDER BY (a)) FROM lead_example;
+------+----------------------------+
| a | LEAD(a) OVER(ORDER BY (a)) |
+------+----------------------------+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | NULL |
+------+----------------------------+SELECT a, LEAD(a,2) OVER(ORDER BY (a)) FROM lead_example;
+------+------------------------------+
| a | LEAD(a,2) OVER(ORDER BY (a)) |
+------+------------------------------+
| 1 | 3 |
| 2 | 4 |
| 3 | NULL |
| 4 | NULL |
+------+------------------------------+Last modified: February 16, 2023