SQL SERVER – Solution to Puzzle – Simulate LEAD() and LAG() without Using SQL Server 2012 Analytic Function
- by pinaldave
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)
Filed under: Pinal Dave, PostADay, Readers Contribution, Readers Question, SQL, SQL Authority, SQL Function, SQL Puzzle, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology
