Lead Function in SQL Server

In this article, we will learn about the concept of the Lead() function.

• The Lead() function is the part of the window function. It is available from the SQL server 2012.
• The Lead() function provides access to the row at the specified offset or position, which will follow the current row.
• It is used to compare the individual row data with the subsequent row data. It means we can access the data of the next row or the row after the next row, and so on.

Syntax,

LEAD(return_value ,offset [,default]) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

Here,

return_value:

It will return the value of the following row from the specified offset.

offset:

offset is the integer number, which defines the row, forward from the current row. which is accessible in the Lead() function.

the offset can be an expression, subquery, or column that evaluates to a positive integer. The default value of the offset is 1 if we don’t specify.

default:

It will return the default value if the specified offset goes beyond the scope of the partition. if the default is not defined then, it will return NULL.

partition by clause:

Partition by clause is used to distribute the result set into the partition, on which the Lead() function is applied.

if we don’t specify the partition by clause the Lead() function will treat the result set as a single partition.

order by clause:

order by clause specified the logical order of each partition, on which the Lead() function is applied.

Example

1. With the use of Partition by clause

SELECT	s.Brand,
    s.Month,
    s.NetSales,
    LEAD(s.NetSales,1) OVER(Partition by s.Brand order by s.Month)
From Sales as s

Output,

2. Without the use of partition by clause.

with SalesData As
(
SELECT Month, sum(NetSales) NetSales
FROM Sales
group by Month
)
select Month, NetSales, LEAD(NetSales,1) OVER (order by Month) PreviousMonthSales
From SalesData

Output,

I hope, this article will help you to understand the concept of the Lead() window function SQL server.

Thank You.

Also, check Lag Function In SQL Server.