In SQL Server, LAG() is a window function that enables us to access a value from a previous row in the same result set, without the need to perform a self-join.
We specify the previous row as an offset from the current row. An offset of 1 means the previous row, an offset of 2 means two rows back, and so on.
Syntax
The official syntax goes like this:
LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause )So scalar_expression can be the name of a column in the query, and we have the option of specifying the offset (offset) and a default value (default) to use in instances where offset exceeds the scope of the partition and there’s no value to be returned.
We must also provide an OVER clause, and that clause must have an ORDER BY clause.
Example
Here’s a basic example to demonstrate:
SELECT
VendorId,
ProductName,
ProductPrice,
LAG( ProductPrice, 1 ) OVER (
ORDER BY ProductPrice
) AS LAG
FROM Products;Result:
VendorId ProductName ProductPrice LAG -------- ------------------------------- ------------ ----- 1004 Bottomless Coffee Mugs (4 Pack) 9.99 null 1003 Hammock 10 9.99 1001 Long Weight (green) 11.99 10 1004 Tea Pot 12.45 11.99 1001 Long Weight (blue) 14.75 12.45 1001 Left handed screwdriver 25.99 14.75 1001 Right handed screwdriver 25.99 25.99 1002 Sledge Hammer 33.49 25.99 1003 Straw Dog Box 55.99 33.49 1003 Chainsaw 245 55.99
Here, I specified that the LAG() function return its value from the ProductPrice column, and I specified an offset of 1.
We can see that the LAG column returns its value from the previous row. On the first row however, it returns null. That’s because there’s no previous row for which to retrieve a value.
Using the Default Offset
We can omit the second argument in order to use the default offset value of 1:
SELECT
VendorId,
ProductName,
ProductPrice,
LAG( ProductPrice ) OVER (
ORDER BY ProductPrice
) AS LAG
FROM Products;Result:
VendorId ProductName ProductPrice LAG -------- ------------------------------- ------------ ----- 1004 Bottomless Coffee Mugs (4 Pack) 9.99 null 1003 Hammock 10 9.99 1001 Long Weight (green) 11.99 10 1004 Tea Pot 12.45 11.99 1001 Long Weight (blue) 14.75 12.45 1001 Left handed screwdriver 25.99 14.75 1001 Right handed screwdriver 25.99 25.99 1002 Sledge Hammer 33.49 25.99 1003 Straw Dog Box 55.99 33.49 1003 Chainsaw 245 55.99
Same result as the previous example. The only difference is in the code we used to produce the result. In the previous example we explicitly specified an offset of 1, whereas in this example we omitted the offset argument altogether in order to use the default offset of 1.
Changing the Offset
Here’s an example of using a different offset:
SELECT
VendorId,
ProductName,
ProductPrice,
LAG( ProductPrice, 3 ) OVER (
ORDER BY ProductPrice
) AS LAG
FROM Products;Result:
VendorId ProductName ProductPrice LAG -------- ------------------------------- ------------ ----- 1004 Bottomless Coffee Mugs (4 Pack) 9.99 null 1003 Hammock 10 null 1001 Long Weight (green) 11.99 null 1004 Tea Pot 12.45 9.99 1001 Long Weight (blue) 14.75 10 1001 Left handed screwdriver 25.99 11.99 1001 Right handed screwdriver 25.99 12.45 1002 Sledge Hammer 33.49 14.75 1003 Straw Dog Box 55.99 25.99 1003 Chainsaw 245 25.99
Here I specified an offset of 3, and so we got the value from three rows back. We also got null in the first three rows of the LAG column as a result.
Change the Default Value
By default, LAG() returns null if the offset is beyond the scope of the partition and there’s no value to be returned (such as when we’re on the first row).
We can provide a third argument to change the default value:
SELECT
VendorId,
ProductName,
ProductPrice,
LAG( ProductPrice, 3, 0 ) OVER (
ORDER BY ProductPrice
) AS LAG
FROM Products;Result:
VendorId ProductName ProductPrice LAG -------- ------------------------------- ------------ ----- 1004 Bottomless Coffee Mugs (4 Pack) 9.99 0 1003 Hammock 10 0 1001 Long Weight (green) 11.99 0 1004 Tea Pot 12.45 9.99 1001 Long Weight (blue) 14.75 10 1001 Left handed screwdriver 25.99 11.99 1001 Right handed screwdriver 25.99 12.45 1002 Sledge Hammer 33.49 14.75 1003 Straw Dog Box 55.99 25.99 1003 Chainsaw 245 25.99
In this case I specified a default value of 0, and so the first three rows return 0 instead of null.
Note that the third argument, if provided, must be type-compatible with the expression provided by the first argument.
Partitioning the Result Set
We can use a PARTITION BY clause with window functions to partition the result set by a given column. For example, we can partition by vendor:
SELECT
v.VendorName,
p.ProductName,
p.ProductPrice,
LAG( ProductPrice ) OVER (
PARTITION BY p.VendorId
ORDER BY p.ProductPrice
) AS LAG
FROM Products p
INNER JOIN Vendors v
ON v.VendorId = p.VendorId;Result:
VendorName ProductName ProductPrice LAG ------------- ------------------------------- ------------ ----- Mars Supplies Long Weight (green) 11.99 null Mars Supplies Long Weight (blue) 14.75 11.99 Mars Supplies Left handed screwdriver 25.99 14.75 Mars Supplies Right handed screwdriver 25.99 25.99 Randy Roofers Sledge Hammer 33.49 null Pedal Medals Hammock 10 null Pedal Medals Straw Dog Box 55.99 10 Pedal Medals Chainsaw 245 55.99 Katty Kittens Bottomless Coffee Mugs (4 Pack) 9.99 null Katty Kittens Tea Pot 12.45 9.99
This time our LAG column resets with each new partition.
In this case I used a SQL join to join the Products and Vendors table so that we got the vendor names instead of just their IDs.
The OVER Clause is Required
As mentioned, we must provide the OVER clause when using window functions like LAG(). Here’s what happens if we remove it:
SELECT
VendorId,
ProductName,
ProductPrice,
LAG( ProductPrice )
FROM Products;Result:
Msg 10753, Level 15, State 1, Line 5 The function 'LAG' must have an OVER clause.
The ORDER BY Clause is Required
And our OVER clause must include an ORDER BY clause. Here’s what happens when we use an OVER clause without an ORDER BY clause:
SELECT
VendorId,
ProductName,
ProductPrice,
LAG( ProductPrice ) OVER( )
FROM Products;Result:
Msg 4112, Level 15, State 1, Line 5 The function 'LAG' must have an OVER clause with ORDER BY.
Documentation
For more information and examples, see Microsoft’s documentation for the LAG() function.