SQL SERVER – Puzzle to Win Print Book – Functions FIRST_VALUE and LAST_VALUE with OVER clause and ORDER BY
- by pinaldave
Some time an interesting feature and smart audience makes total difference at places. From last two days, I have been writing on SQL Server 2012 feature FIRST_VALUE and LAST_VALUE.
Please read following post before I continue today as this question is based on the same.
Introduction to FIRST_VALUE and LAST_VALUE
Introduction to FIRST_VALUE and LAST_VALUE with OVER clause
As a comment of the second post I received excellent question from Nilesh Molankar. He asks what will happen if we change few things in the T-SQL. I really like this question as this kind of questions will make us sharp and help us perform in critical situation in need. We recently publish SQL Server Interview Questions book. I promise that in future version of this book, we will for sure include this question. Instead of repeating his question, I am going to ask something very similar to his question.
Let us first run following query (read yesterday’s blog post for more detail):
USE AdventureWorks
GO
SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
FIRST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY SalesOrderDetailID
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FstValue,
LAST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY SalesOrderDetailID
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LstValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
GO
Here is the resultset of the above query.
Now let us change the ORDER BY clause of OVER clause in above query and see what is the new result.
USE AdventureWorks
GO
SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
FIRST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY OrderQty
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FstValue,
LAST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY OrderQty
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LstValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
GO
Now let us see the result and ready for interesting question:
Puzzle
You can see that row number 2, 3, 4, and 5 has same SalesOrderID = 43667. The FIRST_VALUE is 78 and LAST_VALUE is 77. Now if these function was working on maximum and minimum value they should have given answer as 77 and 80 respectively instead of 78 and 77. Also the value of FIRST_VALUE is greater than LAST_VALUE 77. Why? Explain in detail.
Hint
Let me give you a simple hint. Just for simplicity I have changed the order of columns selected in the SELECT and ORDER BY (at the end). This will not change resultset but just order of the columns as well order of the rows. However, the data remains the same.
USE AdventureWorks
GO
SELECT s.OrderQty,s.SalesOrderID,s.SalesOrderDetailID,
FIRST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY OrderQty
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FstValue,
LAST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY OrderQty
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LstValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.OrderQty,s.SalesOrderID,s.SalesOrderDetailID
GO
Above query returns following result:
Now I am very sure all of you have figured out the solution. Here is the second hint – pay attention to row 2, 3, 4, and 10.
Hint2
T-SQL Enhancements: FIRST_VALUE() and LAST_VALUE()
MSDN: FIRST_VALUE and LAST_VALUE
Rules
Leave a comment with your detailed answer by Nov 15′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
