LAG()

Accesses data from a previous row in the same result set without the use of a self-join starting with. 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.

Signature

LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
    OVER ( [ partition_by_clause ] order_by_clause )

Parameters

  • 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 other 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 *offset* is beyond the scope of the partition. If a default value is not specified, NULL is returned. *default* can be a column, subquery, or other expression, but it cannot be an analytic function. *default* must be type-compatible with *scalar_expression*.

Examples

SELECT product, amount, LAG(amount) OVER (ORDER BY id) AS previous FROM dbo.orders;
product                                  amount       previous
---------------------------------------- ------------ ------------
keyboard                                        49.50         NULL
monitor                                        219.00        49.50
keyboard                                        99.00       219.00
mouse                                           27.75        99.00
monitor                                        438.00        27.75
cable                                           15.00       438.00

Run against SQL Server 16.0.4265.3 on 2026-09-13. This is the output we got, not the documentation’s.

Available on

  • SQL Server 2017 and later
  • Azure SQL Database
  • Azure SQL Managed Instance
  • Azure Synapse Analytics
  • Analytics Platform System (PDW)
  • Microsoft Fabric warehouse
  • Fabric SQL database

Signature and description adapted from the Microsoft SQL documentation, used under the CC BY 4.0. SQL Server and Azure are trademarks of the Microsoft group of companies. This page is not affiliated with Microsoft.