LEAD

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

Was this article helpful?