SQL SERVER – Puzzle to Win Print Book – Functions FIRST_VALUE and LAST_VALUE with OVER clause and ORDER BY

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.

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

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)

22 thoughts on “SQL SERVER – Puzzle to Win Print Book – Functions FIRST_VALUE and LAST_VALUE with OVER clause and ORDER BY

  1. 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.

    Like

  2. 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

    Like

  3. 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

    Like

  4. 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.

    Like

  5. 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.

    Like

  6. 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

    Like

  7. 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.

    Like

  8. 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

    Like

  9. 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.

    Like

  10. 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.

    Like

  11. 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

    Like

  12. 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

    Like

  13. 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

    Like

  14. 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,

    Like

  15. Hi All,

    This was wonderful quiz and we had lots of fun to evaluate it. The real difficulty was to decide who will be the winner of this contest. We first selected all the valid answers and then put the names in simple table. We picked up random name as winner.

    The winner is – P.Anish Shenoy

    Once again, this is not the end of puzzles which we will share with you. We will have many more and the real winning is learning new features.

    Many thanks again,
    Pinal

    Like

    • Thanks a lot for the book sir. Reading your blog post is an addiction. Thanks for helping the SQL Community.

      All the best wishes …. :)

      Thanks and Regards,
      P.Anish Shenoy

      Like

  16. Pingback: SQL SERVER – 2012 – Summary of All the Analytic Functions – MSDN and SQLAuthority « Journey to SQLAuthority

  17. Pingback: SQLAuthority News – An Year Worth Remembering and Looking Forward to Better Next Year « SQL Server Journey with SQLAuthority

  18. Pingback: SQL SERVER – Weekly Series – Memory Lane – #003 « SQL Server Journey with SQL Authority

  19. Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

  20. Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s