SQL SERVER – Solution to Puzzle – Simulate LEAD() and LAG() without Using SQL Server 2012 Analytic Function

Earlier I wrote a series on SQL Server Analytic Functions of SQL Server 2012. During the series to keep the learning maximum and having fun, we had few puzzles. One of the puzzle was simulating LEAD() and LAG() without using SQL Server 2012 Analytic Function.

Please read the puzzle here first before reading the solution : Write T-SQL Self Join Without Using LEAD and LAG. When I was originally wrote the puzzle I had done small blunder and the question was a bit confusing which I corrected later on but wrote a follow up blog post on over here where I describe the give-away.

Quick Recap:

Generate following results without using SQL Server 2012 analytic functions.

I had received so many valid answers. Some answers were similar to other and some were very innovative. Some answers were very adaptive and some did not work when I changed where condition. After selecting all the valid answer, I put them in table and ran RANDOM function on the same and selected winners. Here are the valid answers.

No Joins and No Analytic Functions

Excellent Solution by Geri Reshef – Winner of SQL Server Interview Questions and Answers (India | USA)

WITH T1 AS
(SELECT Row_Number() OVER(ORDER BY SalesOrderDetailID) N,
s.SalesOrderID,
s.SalesOrderDetailID,
s.OrderQty
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663))
SELECT SalesOrderID,SalesOrderDetailID,OrderQty,
CASE WHEN N%2=1 THEN MAX(CASE WHEN N%2=0 THEN SalesOrderDetailID END) OVER (Partition BY (N+1)/2) ELSE MAX(CASE WHEN N%2=1 THEN SalesOrderDetailID END) OVER (Partition BY N/2) END LeadVal,
CASE WHEN N%2=1 THEN MAX(CASE WHEN N%2=0 THEN SalesOrderDetailID END) OVER (Partition BY N/2) ELSE MAX(CASE WHEN N%2=1 THEN SalesOrderDetailID END) OVER (Partition BY (N+1)/2) END LagVal
FROM T1
ORDER BY SalesOrderID,
SalesOrderDetailID,
OrderQty;
GO

No Analytic Function and Early Bird

Excellent Solution by DHall – Winner of Pluralsight 30 days Subscription

-- a query to emulate LEAD() and LAG()
;WITH s AS (
SELECT
1 AS ldOffset, -- equiv to 2nd param of LEAD
1 AS lgOffset, -- equiv to 2nd param of LAG
NULL AS ldDefVal, -- equiv to 3rd param of LEAD
NULL AS lgDefVal, -- equiv to 3rd param of LAG
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS row,
SalesOrderID,
SalesOrderDetailID,
OrderQty
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
)
SELECT s.SalesOrderID,
s.SalesOrderDetailID,
s.OrderQty,
ISNULL( sLd.SalesOrderDetailID, s.ldDefVal) AS LeadValue,
ISNULL( sLg.SalesOrderDetailID, s.lgDefVal) AS LagValue
FROM s
LEFT OUTER JOIN s AS sLd ON s.row = sLd.row - s.ldOffset
LEFT OUTER JOIN s AS sLg ON s.row = sLg.row + s.lgOffset
ORDER BY s.SalesOrderID, s.SalesOrderDetailID, s.OrderQty

No Analytic Function and Partition By

Excellent Solution by DHall – Winner of Pluralsight 30 days Subscription

/* a query to emulate LEAD() and LAG() */
;WITH s AS (
SELECT
1 AS LeadOffset, /* equiv to 2nd param of LEAD */
1 AS LagOffset, /* equiv to 2nd param of LAG */
NULL AS LeadDefVal, /* equiv to 3rd param of LEAD */
NULL AS LagDefVal, /* equiv to 3rd param of LAG */
/* Try changing the values of the 4 integer values above to see their effect on the results */
/* The values given above of 0, 0, null and null
behave the same as the default 2nd and 3rd parameters to LEAD() and LAG() */
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS row,
SalesOrderID,
SalesOrderDetailID,
OrderQty
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
)
SELECT s.SalesOrderID,
s.SalesOrderDetailID,
s.OrderQty,
ISNULL( sLead.SalesOrderDetailID, s.LeadDefVal) AS LeadValue,
ISNULL( sLag.SalesOrderDetailID, s.LagDefVal) AS LagValue
FROM s
LEFT OUTER JOIN s AS sLead
ON s.row = sLead.row - s.LeadOffset
/* Try commenting out this next line when LeadOffset != 0 */
AND s.SalesOrderID = sLead.SalesOrderID
/* The additional join criteria on SalesOrderID above
is equivalent to PARTITION BY SalesOrderID
in the OVER clause of the LEAD() function */
LEFT OUTER JOIN s AS sLag
ON s.row = sLag.row + s.LagOffset
/* Try commenting out this next line when LagOffset != 0 */
AND s.SalesOrderID = sLag.SalesOrderID
/* The additional join criteria on SalesOrderID above
is equivalent to PARTITION BY SalesOrderID
in the OVER clause of the LAG() function */
ORDER BY s.SalesOrderID, s.SalesOrderDetailID, s.OrderQty

No Analytic Function and CTE Usage

Excellent Solution by Pravin Patel - Winner of SQL Server Interview Questions and Answers (India | USA)

--CTE based solution
;
WITH cteMain
AS
(
SELECT
SalesOrderID,
SalesOrderDetailID,
OrderQty,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS sn
FROM
Sales.SalesOrderDetail
WHERE
SalesOrderID IN (43670, 43669, 43667, 43663)
)
SELECT
m.SalesOrderID, m.SalesOrderDetailID, m.OrderQty,
sLead.SalesOrderDetailID AS leadvalue,
sLeg.SalesOrderDetailID AS leagvalue
FROM
cteMain AS m
LEFT OUTER JOIN cteMain AS sLead
ON sLead.sn = m.sn+1
LEFT OUTER JOIN cteMain AS sLeg
ON sLeg.sn = m.sn-1
ORDER BY
m.SalesOrderID, m.SalesOrderDetailID, m.OrderQty

No Analytic Function and Co-Related Subquery Usage

Excellent Solution by Pravin Patel – Winner of SQL Server Interview Questions and Answers (India | USA)

-- Co-Related subquery
SELECT
m.SalesOrderID,
m.SalesOrderDetailID,
m.OrderQty,
(
SELECT MIN(SalesOrderDetailID)
FROM Sales.SalesOrderDetail AS l
WHERE l.SalesOrderID IN (43670, 43669, 43667, 43663)
AND
l.SalesOrderID >= m.SalesOrderID AND l.SalesOrderDetailID > m.SalesOrderDetailID
) AS lead,
(
SELECT MAX(SalesOrderDetailID)
FROM Sales.SalesOrderDetail AS l
WHERE l.SalesOrderID IN (43670, 43669, 43667, 43663)
AND
l.SalesOrderID <= m.SalesOrderID AND l.SalesOrderDetailID < m.SalesOrderDetailID
) AS leag
FROM
Sales.SalesOrderDetail AS m
WHERE
m.SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY
m.SalesOrderID, m.SalesOrderDetailID, m.OrderQty

This was one of the most interesting Puzzle on this blog.

Giveaway

Winners will get following giveaways.

Geri Reshef and Pravin Patel

SQL Server Interview Questions and Answers (India | USA)

DHall

Pluralsight 30 days Subscription

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

About these ads

8 thoughts on “SQL SERVER – Solution to Puzzle – Simulate LEAD() and LAG() without Using SQL Server 2012 Analytic Function

  1. Hi Pinal,

    One Request

    Can you post the list of people who posted correct answer? For others It Would be helpful and know why the post wasn’t correct.. Will be a great learning.. Thanks.

    Regards,
    Rajkumar,
    Bangalore.

  2. Hi Pinal,

    Hello ..I Need Help..I have one table where in one column have name like this
    Q1 Atul Prakash N
    Q2 Ramesh Kumar Singh V
    Q1 Q2 Vishal Kumar V
    Q1 Q3 Q5 Ramsh Yadav V
    I want Only name i.e. Atul Prakash, Ramesh Kumar singh, Vishal Kumar etc. Please provide me optimized solution for this… and one more thing how to compare data with one column of other table of one columns…please help me..

  3. Hi pinal i have off the track question for you.
    Can you please help me to find solution!!!

    I have a one database say “test” in sql server 2008 R2. Now i want password protect that database. Is it possible to password protect individual database. If yes please help to get solution.

    Thank you Pinal
    Looking back for you reply.

  4. Sir,
    Can you please tell me if thousands of user updating or requesting for same table then how can we handle such type of scenario .

  5. How could I change one of the solutions (preferably the first one) to to give me the lead only if the SalesOrderID is the same?

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