Help me please with this:
Thank you in advance,
Yes, you get the last value so far when you only say ORDER BY col. When you leave out the window, this defaults to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
Try this for a variation:
SELECT name, column_id, last_value(name) OVER(ORDER BY column_id ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING ) FROM sys.all_columns WHERE object_id = object_id('sys.objects')
What is the type of the field "Quantity"? If it is not numeric, but rather varchar or nvarchar, then the result of ORDER BY Quantity will be that the largest value is 50 (not 200) because it will be ordered alphabetically. In this case, your LAST_VALUE will be the one associated to Quantity 50, which happens to be iPhone.
I think that the last value of this ordered set is ambiguous because there are three last values: Samsung (200), iPhone (200) and Samsung (200). Maybe other queries will return Samsung instead of iPhone and iPhone instead of Samsung, but it is not certain. Probably you can assume that the server takes one of values arbitrarily.
Thank you all for your replies!
"What is the type of the field "Quantity"?" - INT
"I think that the last value of this ordered set is ambiguous because there are three last values: Samsung (200), iPhone (200) and Samsung (200)." - I thought of it but it's my understanding that if it was the iPhone chosen as the last value the iPhone should have been returned in ALL rows - NOT only from 1 to 7!
I've already tried to get rid of ties before posting:
"Maybe the experiments show that your understanding is incorrect.”." - I don't know how else I can interpret the wording "Returns the last value in an ordered set of values in SQL Server 2012."
Thank you all for your explanations!
It works - just forgot that the default frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW!
select Number, Rank1,
LAST_VALUE(Number) OVER (ORDER BY Rank1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS [Max_Number]