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]

See Also