How To Use LEAD and LAG Function In SQL Server

SQL Server supports numerous helpful analytical functions to perform statistical and computational operations. Analytical functions are straightforward to use and replace complex code, nested queries and self joins which are tough to scale and optimize for best performance. Moreover, in this tech-recipe post, we will learn more on LEAD and LAG analytic functions.

LEAD and LAG, Analytic functions added since SQL Server 2012 edition. The LEAD function allows us to access subsequent rows from the current row. Likewise, the LAG function allows accessing previous rows from the current row.

Indeed important to understand the syntax and arguments used with LEAD and LAG function as following.

Syntax


LEAD(column-name, offset-no-of-forward-lead-rows-to-access, default-value) OVER (PARTITION BY column-name ORDER BY column-name)

LAG(column-name, offset-no-of-backward-lag-rows-to-access, default-value) OVER (PARTITION BY column-name ORDER BY column-name)

Arguments

Column Name – Name of the column where LEAD or LAG function will look to find the value in a forward or backward direction from the current row.

Offset – Number of Next Lead Rows – The number of rows ahead from the current row to fetch the value.

Offset – Number of Previous Lag Rows – The number of rows behind from the current row to fetch the value.

Default Value – In case LEAD/ LAG function crosses the boundary of a partition or there are no values to fetch either from rows forward or backwards then a default value will be return. We can specify the default value, though it is optional if not specified then NULL is returned.

OVER (PARTITION BY) – Optional clause, divides the result set into a different partition based on condition and applies the LEAD/ LAG function to each result set independently.

ORDER BY – This is required to set up order by sorting values before applying LEAD/ LAG function.

LEAD / LAG Function – Points to Ponder

1.LEAD function looks forward from the current row to fetch value.

2.LAG function looks backwards from current row to fetch value.

3.ORDER BY clause is required to have sorted values.

4.The second parameter, number of rows to look forward/ backward, if not specified then default 1 will be considered, therefore, LEAD/ LAG function will look forward/backwards by only 1 row.

5.The third parameter is optional and if not specified then NULL is returned.

6.PARTITION BY clause is optional.

LEAD/ LAG Function – Examples

Let’s demonstrate LEAD/ LAG function using helpful and practical examples.

1.

LEAD Function- With ORDER BY Clause

The following example demonstrates the use of LEAD function. The below query retrieves the sales for next quarter and displays with the current row.

We have specified the offset value to 1 thus LEAD function only looks for subsequent row by moving one place ahead. The default value is specified as 0, therefore instead of showing NULL for the last row, it shows 0.00.

Note that – no value present after 4th quarter, thus 0.00 is displayed next to 12500.00.

USE tempdb;
GO

IF OBJECT_ID('dbo.ProductSales','U') IS NOT NULL
DROP TABLE dbo.ProductSales;

CREATE TABLE dbo.ProductSales
(
  [Year]	INT
 ,[Quarter] TINYINT
 ,Sales     DECIMAL(9,2)
);

INSERT INTO dbo.ProductSales VALUES 
 (2018, 1, 35000.00)
,(2018, 2, 23000.00)
,(2018, 3, 56000.00)
,(2018, 4, 12500.00);

--Lead Function to Fetch Next Sales Value 
SELECT [Year], [Quarter], Sales, 
       LEAD(Sales, 1, 0) OVER (ORDER BY [Quarter] ASC) as [LEAD-Next-Quarter-Sales]	  
FROM   dbo.ProductSales;

Result Set

2

LAG – With ORDER BY Clause

The following example demonstrates the use of LAG function. The query retrieves the sales for the previous quarter and displays with the current row.

We have specified the offset value to 1 thus LAG function only looks for the previous row by moving one place backwards. The default value is specified as 0, therefore instead of showing NULL for the last row, it shows 0.00.

Note that – no value present before the 1st quarter, thus 0.00 is displayed next to 35000.00.

USE tempdb;
GO

IF OBJECT_ID('dbo.ProductSales','U') IS NOT NULL
DROP TABLE dbo.ProductSales;

CREATE TABLE dbo.ProductSales
(
  [Year]	INT
 ,[Quarter] TINYINT
 ,Sales     DECIMAL(9,2)
);

INSERT INTO dbo.ProductSales VALUES 
 (2018, 1, 35000.00)
,(2018, 2, 23000.00)
,(2018, 3, 56000.00)
,(2018, 4, 12500.00);

--LAG Function to Fetch Previous Sales Value 
SELECT [Year], [Quarter], Sales
       ,LAG(Sales, 1, 0)  OVER (ORDER BY [Quarter] ASC) as [LAG-Previous-Quarter-Sales]
FROM   dbo.ProductSales;

Result Set

3.

LEAD – With PARTITION BY Clause And ORDER BY Clause

The following example demonstrates the use of LEAD function with PARTITION BY clause.

The query partition the result set by Year column and orders the rows by Quarter column within Partition. Furthermore, the LEAD function is applied to each partition. Therefore, you will notice that whenever a partition is crossing the boundary, then LEAD function applied again. By not considering the values from other partition.

The query obtains sales for next quarter within each year being a partition. Certainly, 4th quarter is displaying 0.00 sales instead of displaying sales for next year’s 1st quarter, producing correct results.

USE tempdb;
GO

IF OBJECT_ID('dbo.ProductSales','U') IS NOT NULL
DROP TABLE dbo.ProductSales;

CREATE TABLE dbo.ProductSales
(
  [Year]	INT
 ,[Quarter] TINYINT
 ,Sales     DECIMAL(9,2)
);

INSERT INTO dbo.ProductSales VALUES 
 (2017, 1, 35000.00)
,(2017, 2, 23000.00)
,(2017, 3, 56000.00)
,(2017, 4, 12500.00)
,(2018, 1, 11000.00)
,(2018, 2,  9400.00)
,(2018, 3, 84200.00)
,(2018, 4,  1122.00)
,(2019, 1, 55520.00)
,(2019, 2, 64000.00)
,(2019, 3, 62030.00)
,(2019, 4, 52520.00)

--Lead Function With Partition By to Fetch Next Sales Value 
SELECT [Year], [Quarter], Sales, 
       LEAD(Sales, 1, 0) OVER (PARTITION BY [Year] ORDER BY [Year], [Quarter] ASC) as [LEAD-Next-Quarter-Sales]	  
FROM   dbo.ProductSales;

Result Set

4

LAG – With PARTITION BY Clause And ORDER BY Clause

The following example demonstrates the use of LAG function with PARTITION BY clause.

The query partition the result set by Year column and Order the rows by Quarter column within Partition. Furthermore, LAG function is applied to each partition. Therefore, you will notice that whenever Partition is crossing the boundary then LAG function applied again. By not considering the values from other partition.

The query obtains sales for previous quarter within each year being a partition. Certainly, 1st quarter is displaying 0.00 sales instead of displaying sales for previous year’s 4th quarter producing correct results.

USE tempdb;
GO

IF OBJECT_ID('dbo.ProductSales','U') IS NOT NULL
DROP TABLE dbo.ProductSales;

CREATE TABLE dbo.ProductSales
(
  [Year]	INT
 ,[Quarter] TINYINT
 ,Sales     DECIMAL(9,2)
);

INSERT INTO dbo.ProductSales VALUES 
 (2017, 1, 35000.00)
,(2017, 2, 23000.00)
,(2017, 3, 56000.00)
,(2017, 4, 12500.00)
,(2018, 1, 11000.00)
,(2018, 2,  9400.00)
,(2018, 3, 84200.00)
,(2018, 4,  1122.00)
,(2019, 1, 55520.00)
,(2019, 2, 64000.00)
,(2019, 3, 62030.00)
,(2019, 4, 52520.00)

--Lag Function With Partition By to Fetch Previous Sales Value 
SELECT [Year], [Quarter], Sales, 
       LAG(Sales, 1, 0) OVER (PARTITION BY [Year] ORDER BY [Year], [Quarter] ASC) as [LAG-Previous-Quarter-Sales]	  
FROM   dbo.ProductSales;

Result Set

Summary

To summarize, we have learned to use LEAD and LAG analytic functions with ORDER BY clause and PARTITION BY to learn to get the next and previous value from current row. If you like this post you may read through Tech-Recipes Database archive posts to learn some more useful stuff.