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 (https://blog.sqlauthority.com)

SQL Function, SQL Scripts
Previous Post
SQLAuthority News – A Real Story of Book Getting ‘Out of Stock’ to A 25% Discount Story Available
Next Post
SQL SERVER – Puzzle to Win Print Book – Write T-SQL Self Join Without Using LEAD and LAG

Related Posts

45 Comments. Leave new

  • 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.

    Reply
  • Chirag Satasiya
    November 15, 2011 3:56 pm

    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

    Reply
  • 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.

    Reply
  • nice one really………..

    Reply
  • great article !!!

    Reply
  • Rudrashis Karan
    November 17, 2011 12:15 pm

    Dear Sir,
    its really exciting………

    Regards
    Rudrashis Karan

    Reply
  • Really? This is tech from the early 2000s (Oraclee, DB2) that was finally added to ansi sql in 2003, and it merits an article in 2012? This is literally ten year old tech.

    Reply
  • 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?

    Reply
  • Thanks Pinal for clear introduction on LEAD and LAG Functions…

    Reply
  • 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?

    Reply
  • Mangesh Tambare
    January 11, 2012 10:23 pm

    Really nice one sir

    Reply
  • nice one….Good explanation.

    Reply
  • nice article.., good examples and explanation.

    Reply
  • datelligence
    April 4, 2012 12:28 am

    Pinal as good as always but, how about SQL SERVER 2008. I still use it and need to do the same as LAG and LEAD. Thanks

    Reply
  • nice ………………..

    Reply
  • its good article,,,,,,,,,,,,

    Reply
  • Has been available in Oracle for around a decade.

    Reply
  • Do you know anything equivalent of LAG and LEAD in SQL Server 2008 R2?

    Reply
  • 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.

    Reply
  • Hi Pinal,

    I am getting the below error, can you please suggest whats the solution for this.

    Thanks
    Anil.

    Reply

Leave a Reply Cancel reply

Exit mobile version