SQL LEAD Function

The LEAD function returns the next row value from the result set.

Multiple groups can be created by partioning the result set.

In that case, LEAD returns the next value inside each group.

Example

#

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

SELECT CONCAT(FirstName, ' ', LastName) AS Customer, 
       TotalAmount, OrderDate,
       LEAD(OrderDate) OVER(ORDER BY OrderDate) AS 'Next Order Date'
  FROM [Order] O
  JOIN Customer C ON O.CustomerId = C.Id
Result:  408 records
Customer TotalAmount OrderDate Next Order Date
Patricia McKenna 3127.00 2012-09-05 00:00:00.000 2012-09-06 00:00:00.000
Janete Limeira 349.50 2012-09-06 00:00:00.000 2012-09-09 00:00:00.000
Giovanni Rovelli 608.00 2012-09-09 00:00:00.000 2012-09-09 00:00:00.000
Rita Müller 755.00 2012-09-09 00:00:00.000 2012-09-10 00:00:00.000
Pascale Cartrain 2708.80 2012-09-10 00:00:00.000 2012-09-11 00:00:00.000
José Pedro Freyre 1242.00 2012-09-11 00:00:00.000 2012-09-12 00:00:00.000

Syntax

#

Syntax of the LEAD function.

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

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

offset -- optional. The number of rows next to the current row.

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

PARTITION BY expression -- optional. Organizes rows into different groups where the LEAD 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

LEAD with PARTITION

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

LEAD with OFFSET

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

You may also like



Guides


vsn 3.1