LEAD

Accesses data from a subsequent row in the same result set without the use of a self-join. LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in the following row.

Syntax

LEAD ( scalar_expression [ ,offset ] , [ default ] )
    OVER ( [ partition_by_clause ] order_by_clause )

Arguments

_scalar_expression_

The value to be returned based on the specified offset. It is an expression of any type that returns a single (scalar) value. scalar_expression cannot be an analytic function.

_offset_

The number of rows forward from the current row from which to obtain a value. If not specified, the default is 1. _offset_ can be a column, subquery, or another expression that evaluates to a positive integer or can be implicitly converted to bigint. _offset_ cannot be a negative value or an analytic function.

_default_

The value to return when _scalar_expression_ at offset is NULL. If a default value is not specified, NULL is returned. _default_ can be a column, subquery, or another expression, but it cannot be an analytic function. _default_ must be type-compatible with _scalar_expression_.

Return types

The data type of the specified _scalar_expression_. NULL is returned if _scalar_expression_ is nullable or _default_ is set to NULL.

Example

SELECT LEAD(IntColumn) OVER(ORDER BY [IntColumn]) AS [ReturnValueLag] FROM [IntTable]

See Also