LEAD OVER

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

LAG OVER

MAX OVER

⌘I

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Example

​Syntax

​Parameters

​Example

Syntax

Parameters

Example