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.
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.
Let us fun following query.
LEAD(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID
LAG(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
Above query will give us following result.
Now use T-SQL Self Join where same table is joined to itself and get the same result without using LEAD or LAG functions.
Reference: Pinal Dave (http://blog.SQLAuthority.com)