In SQL Server, LEAD() is a window function that allows us to access a value from a later row in the same result set, without the need to perform a self-join.
We specify the row as an offset from the current row. An offset of 1 means it gets the value from the next row, an offset of 2 means two rows forward, and so on.
There’s also a LAG() function which works the same, except that it gets its value from a previous row.
Syntax
The syntax for the LEAD() function goes like this:
LEAD ( 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,
LEAD( ProductPrice, 1 ) OVER (
ORDER BY ProductPrice
) AS LEAD
FROM Products;Result:
VendorId ProductName ProductPrice LEAD -------- ------------------------------- ------------ ----- 1004 Bottomless Coffee Mugs (4 Pack) 9.99 10 1003 Hammock 10 11.99 1001 Long Weight (green) 11.99 12.45 1004 Tea Pot 12.45 14.75 1001 Long Weight (blue) 14.75 25.99 1001 Left handed screwdriver 25.99 25.99 1001 Right handed screwdriver 25.99 33.49 1002 Sledge Hammer 33.49 55.99 1003 Straw Dog Box 55.99 245 1003 Chainsaw 245 null
Here, I specified that the LEAD() function get its value from the ProductPrice column, and I specified an offset of 1.
We can see that the LEAD column returns its value from the next row. Each column contains the same value that the ProductPrice column contains on the next row.
On the last row we get null. That’s because there’s no subsequent 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,
LEAD( ProductPrice ) OVER (
ORDER BY ProductPrice
) AS LEAD
FROM Products;Result:
VendorId ProductName ProductPrice LEAD -------- ------------------------------- ------------ ----- 1004 Bottomless Coffee Mugs (4 Pack) 9.99 10 1003 Hammock 10 11.99 1001 Long Weight (green) 11.99 12.45 1004 Tea Pot 12.45 14.75 1001 Long Weight (blue) 14.75 25.99 1001 Left handed screwdriver 25.99 25.99 1001 Right handed screwdriver 25.99 33.49 1002 Sledge Hammer 33.49 55.99 1003 Straw Dog Box 55.99 245 1003 Chainsaw 245 null
So we got the same result we got in the previous example.
Changing the Offset
Let’s change the offset to 3:
SELECT
VendorId,
ProductName,
ProductPrice,
LEAD( ProductPrice, 3 ) OVER (
ORDER BY ProductPrice
) AS LEAD
FROM Products;Result:
VendorId ProductName ProductPrice LEAD -------- ------------------------------- ------------ ----- 1004 Bottomless Coffee Mugs (4 Pack) 9.99 12.45 1003 Hammock 10 14.75 1001 Long Weight (green) 11.99 25.99 1004 Tea Pot 12.45 25.99 1001 Long Weight (blue) 14.75 33.49 1001 Left handed screwdriver 25.99 55.99 1001 Right handed screwdriver 25.99 245 1002 Sledge Hammer 33.49 null 1003 Straw Dog Box 55.99 null 1003 Chainsaw 245 null
Here I specified an offset of 3, and so we got the value from three rows forward. We also got null in the last three rows of the LEAD column as a result.
Change the Default Value
By default, the LEAD() function returns null if the offset is beyond the scope of the partition and there’s no value to be returned. We can see this happening in the last three rows of the previous example.
We can provide a third argument to change the default value:
SELECT
VendorId,
ProductName,
ProductPrice,
LEAD( ProductPrice, 3, 0 ) OVER (
ORDER BY ProductPrice
) AS LEAD
FROM Products;Result:
VendorId ProductName ProductPrice LEAD -------- ------------------------------- ------------ ----- 1004 Bottomless Coffee Mugs (4 Pack) 9.99 12.45 1003 Hammock 10 14.75 1001 Long Weight (green) 11.99 25.99 1004 Tea Pot 12.45 25.99 1001 Long Weight (blue) 14.75 33.49 1001 Left handed screwdriver 25.99 55.99 1001 Right handed screwdriver 25.99 245 1002 Sledge Hammer 33.49 0 1003 Straw Dog Box 55.99 0 1003 Chainsaw 245 0
Here, I specified a default value of 0, which resulted in the last three rows returning 0 instead of null.
Note that the third argument, if provided, must be type-compatible with the expression provided by the first argument.
Therefore, the following example fails:
SELECT
VendorId,
ProductName,
ProductPrice,
LEAD( ProductPrice, 3, 'No Value' ) OVER (
ORDER BY ProductPrice
) AS LEAD
FROM Products;Result:
Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.
This example failed because I passed a string as the third argument, but the first column is actually defined as decimal(8,2).
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,
LEAD( ProductPrice ) OVER (
PARTITION BY p.VendorId
ORDER BY p.ProductPrice
) AS LEAD
FROM Products p
INNER JOIN Vendors v
ON v.VendorId = p.VendorId;Result:
VendorName ProductName ProductPrice LEAD ------------- ------------------------------- ------------ ----- Mars Supplies Long Weight (green) 11.99 14.75 Mars Supplies Long Weight (blue) 14.75 25.99 Mars Supplies Left handed screwdriver 25.99 25.99 Mars Supplies Right handed screwdriver 25.99 null Randy Roofers Sledge Hammer 33.49 null Pedal Medals Hammock 10 55.99 Pedal Medals Straw Dog Box 55.99 245 Pedal Medals Chainsaw 245 null Katty Kittens Bottomless Coffee Mugs (4 Pack) 9.99 12.45 Katty Kittens Tea Pot 12.45 null
This time our LEAD 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 LEAD(). Here’s what happens if we remove it:
SELECT
VendorId,
ProductName,
ProductPrice,
LEAD( ProductPrice )
FROM Products;Result:
Msg 10753, Level 15, State 1, Line 5 The function 'LEAD' 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,
LEAD( ProductPrice ) OVER( )
FROM Products;Result:
Msg 4112, Level 15, State 1, Line 5 The function 'LEAD' must have an OVER clause with ORDER BY.
Documentation
For more information and examples, see Microsoft’s documentation for the LEAD() function.