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]