This documentation is related to an older version of Firebolt. For the most current documentation, see Firebolt documentation.

LEAD

Returns values from the row after the current row within the requested window.

For more information on usage, please refer to Window Functions.

Syntax

LEAD ( <val> [, <offset> [, <default> ] )
    OVER ( [ PARTITION BY <exp> ] ORDER BY <exp> [ { ASC | DESC } ] )

Parameter	Description
`<val>`	Any valid expression that will be returned based on the `LEAD` `<offset>.`
`<expr>`	The expression used for the `PARTITION BY` clause.
`<offset>`	The number of rows forward from the current row from which to obtain a value.
`<default>`	The expression to return when the offset goes out of the bounds of the window. Supports any expression whose type is compatible with expression. The default is `NULL`.

Example

In the example below, the LEAD function is being used to find the students in each grade level who are sitting next to each other. In some cases, a student does not have an adjacent classmate, so the LEAD function returns NULL.

SELECT
	first_name,
	grade_level,
	LEAD(first_name, -1) OVER (PARTITION BY grade_level ORDER BY first_name ) AS to_the_left,
	LEAD(first_name, 1) OVER (PARTITION BY grade_level ORDER BY first_name ) AS to_the_right
FROM
	class_test;

Returns:

+------------+-------------+-------------+--------------+
| first_name | grade_level | to_the_left | to_the_right |
+------------+-------------+-------------+--------------+
| Frank      |           9 | NULL        | Humphrey     |
| Humphrey   |           9 | Frank       | Iris         |
| Iris       |           9 | Humphrey    | Jojo         |
| Sammy      |           9 | Peter       | NULL         |
| Peter      |           9 | Jojo        | Sammy        |
| Jojo       |           9 | Iris        | Peter        |
| Brunhilda  |          12 | NULL        | Charles      |
| Franco     |          12 | Charles     | Gary         |
| Thomas     |          12 | Jesse       | NULL         |
| Gary       |          12 | Franco      | Jesse        |
| Charles    |          12 | Brunhilda   | Franco       |
| Jesse      |          12 | Gary        | Thomas       |
| Roseanna   |          11 | Otis        | Shangxiu     |
| Carol      |          11 | NULL        | Larry        |
| Wanda      |          11 | Shangxiu    | NULL         |
| Shangxiu   |          11 | Roseanna    | Wanda        |
| Larry      |          11 | Carol       | Otis         |
| Otis       |          11 | Larry       | Roseanna     |
| Deborah    |          10 | Albert      | Mary         |
| Yolinda    |          10 | Shawn       | NULL         |
| Albert     |          10 | NULL        | Deborah      |
| Mary       |          10 | Deborah     | Shawn        |
| Shawn      |          10 | Mary        | Yolinda      |
+------------+-------------+-------------+--------------+