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 ( <expression> [, <offset> [, <default> ]] )
OVER ( [ PARTITION BY <partition_by> ] ORDER BY <order_by> [ { ASC | DESC } ] )
Parameters
| Parameter | Description | Supported input types |
|---|
<expression> | Any valid expression that will be returned based on the <offset>. | Any |
<offset> | The number of rows forward from the current row from which to obtain a value. A negative number will act as LAG(). The offset must be a literal INTEGER. | INTEGER |
<default> | The expression to return when the offset goes out of the bounds of the window. Must be a literal of the same type as <expression>. The default is NULL. | Any |
<partition_by> | The expression used for the PARTITION BY clause. | Any |
<order_by> | An expression used for the ORDER BY clause. | Any |
Example
In the example below, LEAD returns the nickname of the player one position behind (player_before) and one position ahead (player_after) in alphabetical order within each level. NULL is returned when no row exists at the given offset.
SELECT
nickname,
level,
LEAD(nickname, -1) OVER (PARTITION BY level ORDER BY nickname) AS player_before,
LEAD(nickname, 1) OVER (PARTITION BY level ORDER BY nickname) AS player_after
FROM
(VALUES
('kennethpark', 9),
('rileyjon', 9),
('sabrina21', 9),
('ymatthews', 9)
) AS t(nickname, level);
| nickname text | level int | player_before text null | player_after text null |
|---|
| kennethpark | 9 | NULL | rileyjon |
| rileyjon | 9 | kennethpark | sabrina21 |
| sabrina21 | 9 | rileyjon | ymatthews |
| ymatthews | 9 | sabrina21 | NULL |
Rows: 4Execution time: 7.64ms
