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]