SQL SERVER – Puzzle to Win Print Book – Write T-SQL Self Join Without Using FIRST _VALUE and LAST_VALUE

Posted by pinaldave on SQL Authority See other posts from SQL Authority or by pinaldave
Published on Wed, 16 Nov 2011 01:30:23 +0000 Indexed on 2011/11/16 1:58 UTC
Read the original article Hit count: 652

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:

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)


Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

© SQL Authority or respective owner

Related posts about Pinal Dave

Related posts about PostADay