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 (https://blog.sqlauthority.com)
17 Comments. Leave new
Because the window in your windowing function is defined by the Partition of SalesOrderID but ordered by OrderQty not the SalesOrderDetailID. That is what determines the first and last value you will get for each row and . Then the query results are reordered at output by the final Order By clause which interjects the SalesOrderDetailID in front of the OrderQty causing the odd looking results.
Dear Sir,
I think the order by in over clause is arranging the result according to the OrderQty with will result in arrangement in ascending order.
i.e we have values OrderQty 1,1,1,3 with SalesOrderDetailsID as 78,79,80,77 respectively.
So the first_value is 78 and last_value is 77 in the result set when we have order by OrderQty in the Over clause.
Lastly final ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty is only used to arrange the final result set, so that the result is understandable.
It arranges the result set in ascending order first by s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty respectively.
Your Sincerely,
Yeou Sunn Liu
This is really an interesting question.
At the time we had “ORDER BY OrderQty” statement, It first sort records based on OrderQty column in Ascending order so first, all records with qty. 1 (lowest qty) would come and than orders with qty. 3 (maximum records of highest qty) would come. Now, if you will see the first value (minimum value) of SalesOrderDetailsID whose OrderQty is 1, you would find 78 which would be our Firstvalue detected by the function then it comes to the biggest OrderQty which is 3 and if you find maximum number of salesOrderDetailsID whose OrderQty is 3 then you would find 77.
This is the reason we are getting FirstValue as 78 and LastValue as 77
Dear Pinal,
Using order by on orderqty first, arrange the whole data in ascending order of orderqty field which give us values 1,1,1,3 with SalesOrderDetailsID as 78,79,80,77 respectively (only for SalesOrderID =43667)
So it shows,the first_value is 78 and last_value is 77 in the result set.
Nice puzzle! :)
First_Value and Last_Value function is applied on SalesOrderDetailID with ordering on OrderQty.
First_Value returns the first value (78) available for minimum value (i.e.
1) in OrderQty.
Last_Value returns the Last value (77) available for maximum value (i.e. 3) in OrderQty.
Hi Sir,
Based on the query First value and last value are taken based on the order by clause on quantity.
The first sorted value for a particular partition by the number of quantity will be the first value
and similarly the last sorted value for a particular partition by the number of quantity will be the last value.
So according to the query and image, we are querying the first value and last value of ‘SalesOrderDetailId’ with a partition clause on ‘SalesOrderId’ and Order by clause on ‘OrderQty’.
For SalesOrderId = 43667
First Value will be = 78 and Last Value will be = 77
similarly
For SalesOrderId = 43670
First Value will be = 111 and Last Value will be = 113
One Guess here since i have not installed the SQL 2012 i am not able to test the query, according to understanding from the blog i think,
The ORDER BY keyword sort the records in ascending order by default,
if we force the order by keyword to sort the records in decending order.
Then for the same scenario above.
if we run the following query
USE AdventureWorks
GO
SELECT s.OrderQty,s.SalesOrderID,s.SalesOrderDetailID,
FIRST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY OrderQty DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FstValue,
LAST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY OrderQty DESC
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
i guess
for SalesOrderId = 43667
First Value will be = 77 and Last Value will be = 80
similarly
For SalesOrderId = 43670
First Value will be = 112 and Last Value will be = 111
please correct me if i am wrong.
Nice Puzzle enjoying your blog posts on sql 2012 analytic functions sir.
Thanks and Regards,
P.Anish Shenoy
Interesting Puzzle.
OVER (PARTITION BY SalesOrderID
ORDER BY OrderQty
The PARTITION BY clause partitions the SalesOrderDetail by SalesOrderID and the FIRST_VALUE and LAST_VALUE function is applied to each partition independently. The ORDER BY clause specified in the OVER clause determines the logical order in which the FIRST_VALUE or LAST_VALUE function is applied to the rows in each partition. The ROWS UNBOUNDED PRECEDING clause specifies the starting point of the window as the first row of the partition.The ROWS UNBOUNDED FOLLOWING clause specifies the ending point of the window as the last row of the partition.
order by orderqty first, arrange the whole orderqty in ascending like that
SalesOrderID SalesOrderDetailID OrderQty
43667 78 1
43667 79 1
43667 80 1
43667 77 3
if you will see the first value of SalesOrderDetailID it’s 78 with OrderQty is 1,
last value of SalesOrderDetailID = 77 with OrderQty is 1.
It is due to “Order By” clause in –
FIRST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY OrderQty
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FstValue
And in LAST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY OrderQty
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LstValue
When we are using Order By Closes for “OrderQty” column its short the records as –
43667 78 1
43667 79 1
43667 80 1
43667 77 3
and work on it. (giving example for SalesOrderID – 43667 only.)
If you look on the above result First Value for SalesOrderDetailid will be – 78 and Last value will be 77, that’s why the Puzzle question is returning this type of result.
So, the FIRST_VALUE and LAST_VALUE is NOT working as MAX and MIN function respectively.
Nilesh Molankar question is very useful in solving the puzzle.
Thanks & regards
Kundan
Hi,
Since You have mentioned “FIRST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY OrderQty” It will partition the result by SalesOrderDetailID and order it by “OrderQty”.. Which will order itself like this
43667 78 1
43667 79 1
43667 80 1
43667 77 3
From which you can see first value is 78 & last value is 77..
Hence the result is 78 & 77, and order in final result is because of the orderby at the end of the query.
As per the query, The result is ordered by OrderQty first then by SalesOrderID and then by SalesOrderDetailID.
To evalue First_VALUE it considers the first value which comes for each SalesOrderDetailsID since we used the expression “FIRST_VALUE(SalesOrderDetailID)” which is partitioned by SalesOrderID.
Similarly To evalue Last_VALUE it considers the last value which comes for each SalesOrderDetailsID since we used the expression “LAST_VALUE(SalesOrderDetailID)” which is partitioned by SalesOrderID.
Though the row numbers 2,3,4 and 5 has the same Sales Order the First Value is shown as 78 is because result set are ordered based on OrderQty i.e. 1 comes before 3.
OrderQty Sales Order ID Sales Order DetailID Fst Value Lst Value
1 43667 78 78 77
1 43667 79 78 77
1 43667 80 78 77
3 43667 77 78 77
Dear Pinal Sir,
Thank you for making this concept as puzzle.
With this puzzle concept of First_Value and Last_Value is getting more cleared.
You have changed this query (From Hint1):
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
1. Now Order of Select query is depend upon OrderQty column which is not a single value.
2. Now its cleared that First_value and Last_Value is not purely based on Min and Max value.
3. It is simply First Data and Last Data , Whether it is large value or small.
4. Second group of data is re-arranged by QrderQty like:
1 | 43667 | 78
1 | 43667 | 79
1 | 43667 | 80
3 | 43667 | 77
From above reselt set First_Value is 78 and Last Value is 77.
Regard$
Chirag Satasiya
Hello Penal,
Well not even know detail about SQL Server 2012 only the future edition thats it.
Looking at the this + previous 2 article. I came to conclusion that
* The Order by clause at the end of statement is just sorting result produced by query
* FstValue & LstValue are generated based on – data partitined using salesorderdetailid but order by orderqty
as a result,
for salesorderid 43667 ,
there is qty order i.e. 1, 1, 1, 3 as it is ordered in the first_value()
so fstvalue is id of first salesorderdetailid that is 78 which min in qty
and lstvalue is id of salesorderdetailid that is which as max qty 3 .. so ID is 77
and above same logic applies for remaining salesorderid
Regards,
Sachin M
Hi Pinal,
writting mistake.
Point no 2 kindly correct in my above post.
* FstValue & LstValue are generated based on – data partitined using salesorderid but order by orderqty
Regards,
hello all,
I am new in SQL Server. I understood the concept of ordering and getting the First value and Last value but i want to know what is the UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING?