LAG
Accesses data from a previous row in the same result set without the use of a self-join. LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.
Syntax
LAG (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 back 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.
Examples
SELECT LAG(IntColumn) OVER(ORDER BY [IntColumn]) AS [ReturnValueLag] FROM [IntTable]