`LEAD`

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Returns <value> evaluated at the row that’s <offset> rows after the current row within the partition.

Syntax

1lead(<value> [, <offset> [, <default> ]]) OVER ( [ PARTITION BY <expression> [, ...] ] [ ORDER BY <expression> [, ...] ] )

<value>: The expression to evaluate at the target row. The result type matches this expression’s type.

<offset>: The number of rows after the current row. Default: 1.
<default>: The value to return when the target row doesn’t exist, for example when the offset exceeds the end of the partition. Must be the same type as <value>. Default: NULL.

Returns the same type as <value>.

Null handling:

If there’s no row <offset> rows after the current row, lead returns <default> instead.
If <default> is omitted, it defaults to NULL.

Both <offset> and <default> are evaluated with respect to the current row, not the target row, so they can reference the current row’s own column values.
lead depends on the row order defined by ORDER BY in the OVER clause and looks forward through that order.
lead operates over the whole partition and the ORDER BY ordering; it ignores any window frame clause.
For information about the full OVER clause syntax, including PARTITION BY and frame clauses, see Window Functions.
To name a windowing behavior and reuse it across multiple window function calls, use a WINDOW clause. See SELECT.

Returns each employee’s salary alongside the salary of the next employee in descending salary order within the same department.

1SELECT depname, empno, salary, lead(salary) OVER (PARTITION BY depname ORDER BY salary DESC)
2FROM empsalary;

Returns each employee’s salary alongside the salary two positions ahead in descending salary order. Returns 0 if the row doesn’t exist.

1SELECT empno, salary, lead(salary, 2, 0) OVER (ORDER BY salary DESC)
2FROM empsalary;

DID THIS ARTICLE SOLVE YOUR ISSUE?
Let us know so we can improve!