SQL SERVER – Introduction to LEAD and LAG – Analytic Functions Introduced in SQL Server 2012

SQL Server 2012 introduces new analytical function LEAD() and LAG(). These functions accesses data from a subsequent row (for lead) and previous row (for lag) in the same result set without the use of a self-join . It will be very difficult to explain this in words so I will attempt small example to explain you this function. Instead of creating new table, I will be using AdventureWorks sample database as most of the developer uses that for experiment.

Let us fun following query.
USE AdventureWorks
GO
SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
LEAD(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID
) LeadValue,
LAG(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID
) LagValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
GO

Above query will give us following result.

When we look at above resultset it is very clear that LEAD function gives us value which is going to come in next line and LAG function gives us value which was encountered in previous line. If we have to generate the same result without using this function we will have to use self join. In future blog post we will see the same. Let us explore this function a bit more. This function not only provide previous or next line but it can also access any line before or after using offset.

Let us fun following query, where LEAD and LAG function accesses the row with offset of 2.

USE AdventureWorks
GO
SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
LEAD(SalesOrderDetailID,2) OVER (ORDER BY SalesOrderDetailID
) LeadValue,
LAG(SalesOrderDetailID,2) OVER (ORDER BY SalesOrderDetailID
) LagValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
GO

Above query will give us following result.

You can see the LEAD and LAG functions  now have interval of  rows when they are returning results. As there is interval of two rows the first two rows in LEAD function and last two rows in LAG function will return NULL value. You can easily replace this NULL Value with any other default value by passing third parameter in LEAD and LAG function.

Let us fun following query.

USE AdventureWorks
GO
SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
LEAD(SalesOrderDetailID,2,0) OVER (ORDER BY SalesOrderDetailID
) LeadValue,
LAG(SalesOrderDetailID,2,0) OVER (ORDER BY SalesOrderDetailID
) LagValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
GO

Above query will give us following result, where NULL are now replaced with value 0.

Just like any other analytic function we can easily partition this function as well. Let us see the use of PARTITION BY in this clause.

USE AdventureWorks
GO
SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
LEAD(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY SalesOrderDetailID
) LeadValue,
LAG(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY SalesOrderDetailID
) LagValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
GO

Above query will give us following result, where now the data is partitioned by SalesOrderID and LEAD and LAG functions are returning the appropriate result in that window. As now there are smaller partition in my query, you will see higher presence of NULL.

In future blog post we will see how this functions are compared to SELF JOIN.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

36 thoughts on “SQL SERVER – Introduction to LEAD and LAG – Analytic Functions Introduced in SQL Server 2012

  1. Brilliant, in just 2 weeks time I have seen a solution for problems that had been haunting me for months. Well, not really problems but inefficient queries and subqueries, these will definitely make those sleeker, more readable and efficient.

  2. Hi pinal sir,
    Its really exiting to learn all this advanced features of SQL server 2012.

    Thank you again for this updates.

    Regard$
    Chirag Satasiya

  3. Looks like there is a lot of analytics based function in SQL Server 2012, this would make analytics in T-SQL a possibility and would add some value from a BI perspective. These functions could make certain operations which done in a multi step fashion be more efficient.
    Thanks Pinal for the Updates.

  4. Hi Pinal,
    I have got some doubt in conversion of varbinary to varchar. The problem is, I am actually replicating a pdf file(saved as varbinary) from one db to another db through dynamic query but I am getting an error varbinary datatype is incompatible with add operator.Also explicitly mentioning convert varchar() gives me a unidentified file type!! Any possible solution for this problem?

  5. Pingback: SQL SERVER – 2012 – Summary of All the Analytic Functions – MSDN and SQLAuthority « Journey to SQLAuthority

  6. Hi can you help me in this requirement below, how do I write a query.
    I have a table that has 1-24 hours of sales information, Table has date, store name, store location, Intrvl_1, sales $$. Like this I have 24 rows for one day for one store, I need those store where there was a consistent sales $$ (or 5% deviation is OK) between Intrvl_9 to Intrvl_15.
    Any help?

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #003 « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #004 « SQL Server Journey with SQL Authority

  9. you would need to do a self join, or create two data sets with row numbers for each record ordered by date for example. then join on row number minus 1, which is equivalent for lag of 1.

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

  11. Error Message:

    Msg 195, Level 15, State 10, Line 2
    ‘LEAD’ is not a recognized built-in function name.

    Sorry this is the error I am getting when I am trying to execute the below query.

    USE AdventureWorks2008R2
    GO
    SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
    LEAD(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID
    ) LeadValue,
    LAG(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID
    ) LagValue
    FROM Sales.SalesOrderDetail s
    WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
    ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
    GO

    Thanks
    Anil

  12. Pingback: SQL SERVER – How to Access the Previous Row and Next Row value in SELECT statement? | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s