Last week we asked a puzzle SQL SERVER – Puzzle to Win Print Book – Functions FIRST_VALUE and LAST_VALUE with OVER clause and ORDER BY . This puzzle got very interesting participation. The details of the winner is listed here.

In this puzzle we received two very important feedback.

1. This puzzle cleared the concepts of First_Value and Last_Value to the participants.
2. As this was based on SQL Server 2012 many could not participate it as they have yet not installed SQL Server 2012.

I really appreciate the feedback of user and decided to come up something as fun and helps learn new feature of SQL Server 2012.

Please read yesterday’s blog post SQL SERVER – Introduction to LEAD and LAG – Analytic Functions Introduced in SQL Server 2012 before continuing this puzzle as it is based on yesterday’s post.

Yesterday I ran following query which uses functions LEAD and LAG.

USE AdventureWorks
GO
SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
FIRST_VALUE(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID) FstValue,
LAST_VALUE(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID) LstValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
GO


The above query will give us the following result:

Puzzle:

Now use T-SQL Self Join where same table is joined to itself and get the same result without using LEAD or LAG functions.

Hint:

• Introduction to JOINs – Basic of JOINs
• Self Join
• A new analytic functions in SQL Server Denali CTP3 – LEAD() and LAG()

Rules

• Leave a comment with your detailed answer by Nov 21's blog post.
• Open world-wide (where Amazon ships books)
• If you blog about puzzle’s solution and if you win, you win additional surprise gift as well.

Prizes

Print copy of my new book SQL Server Interview Questions Amazon|Flipkart

If you already have this book, you can opt for any of my other books SQL Wait Stats [Amazon|Flipkart|Kindle] and SQL Programming [Amazon|Flipkart|Kindle].

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