SQL LAG Function

The LAG function returns the previous row value from the result set.

Multiple groups can be created by partioning the result set.

In that case, LAG returns the previous value inside each group.

Example

#

List all orders. In each row include the previous order date.

SELECT CONCAT(FirstName, ' ', LastName) AS Customer, 
       TotalAmount, OrderDate,
       LAG(OrderDate) OVER(ORDER BY OrderDate) AS 'Previous Order Date'
  FROM [Order] O
  JOIN Customer C ON O.CustomerId = C.Id
Result:  408 records
Customer TotalAmount OrderDate Previous Order Date
Ann Devon 3063.00 2013-01-01 00:00:00.000 NULL
Paula Wilson 3868.60 2013-01-01 00:00:00.000 2013-01-01 00:00:00.000
Roland Mendel 2713.50 2013-01-02 00:00:00.000 2013-01-01 00:00:00.000
Roland Mendel 1005.90 2013-01-03 00:00:00.000 2013-01-02 00:00:00.000
Giovanni Rovelli 1675.00 2013-01-03 00:00:00.000 2013-01-03 00:00:00.000

Syntax

#

Syntax of the LAG function.

LAG(scalar-expression, offset, default) 
  OVER( PARTITION BY expression, ...
        ORDER BY expression, ... )

scalar-expression -- a value, column or expression returning a single value.

offset -- optional. The offset number of prior rows from the current row.

default -- optional. The value, column, subquery, or expression to return when offset is not present in the partition. If not specified it will return NULL.

PARTITION BY expression -- optional. Organizes rows into different groups where the LAG will be applied. If not specified, the entire result set represents a single group.

ORDER BY expression -- sets the sort order of the result set.

More Examples

LAG with PARTITION

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List monthly sales for the year 2013. For each month include prior month's sales. If there is no prior month, return 0. Also include the delta (difference) between the current and the prior month's sales.
SELECT MONTH(OrderDate) AS [Month], SUM(TotalAmount) AS Sales,
       LAG(SUM(TotalAmount), 1, 0) 
           OVER(ORDER BY MONTH(OrderDate)) AS 'Previous Month',
       SUM(TotalAmount) - LAG(SUM(TotalAmount), 1, 0) 
           OVER(ORDER BY MONTH(OrderDate)) AS Delta
  FROM [Order]
 WHERE YEAR(OrderDate) = 2013
 GROUP BY MONTH(OrderDate)
Result:  12 records
Month Sales Previous Month Delta
1 66692.80 0.00 66692.80
2 41207.20 66692.80 -25485.60
3 39979.90 41207.20 -1227.30
4 55699.39 39979.90 15719.49
5 56823.70 55699.39 1124.31
6 39088.00 56823.70 -17735.70
7 55464.93 39088.00 16376.93
8 49981.69 55464.93 -5483.24
9 59733.02 49981.69 9751.33
10 70328.50 59733.02 10595.48
11 45913.36 70328.50 -24415.14
12 77476.26 45913.36 31562.90

LAG with OFFSET

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List the monthly sales for the year 2013. For each month, include the sales from 6 months ago.
SELECT MONTH(OrderDate) AS [Month], 
       SUM(TotalAmount) AS 'Monthly Sales',
       LAG(SUM(TotalAmount), 6) 
           OVER(ORDER BY MONTH(OrderDate)) AS '6 Months ago'
  FROM [Order]
 WHERE YEAR(OrderDate) = 2013
 GROUP BY MONTH(OrderDate)
Result:  12 records
Month Monthly Sales 6 Months Ago
1 66692.80 NULL
2 41207.20 NULL
3 39979.90 NULL
4 55699.39 NULL
5 56823.70 NULL
6 39088.00 NULL
7 55464.93 66692.80
8 49981.69 41207.20
9 59733.02 39979.90
10 70328.50 55699.39
11 45913.36 56823.70
12 77476.26 39088.00

You may also like



Guides


vsn 3.1