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.

**SQL Server Interview Questions and Answers (India | USA)**

**Pluralsight 30 days Subscription**

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

Hi Pinal,

Thank you very much… for selecting me as winner..

Regards and best wishes…

Pravin Patel.

LikeLike

Well- such a compliment from you – that is the best award.

Thank you very much.

LikeLike

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.

LikeLike

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

LikeLike

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.

LikeLike

Allow only for the people you want them to get access

LikeLike

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 .

LikeLike

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?

LikeLike

Doesn’t the usage of over, order by, and partition by clauses mean the evaluation being performed is an analytic function?

LikeLike

Hi,

have you developed a custom Lead & Lag function to work in SQL Server 2008?

LikeLike