Hello,
Help me please with this:
Thank you in advance,
Michael
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')`
It seems @MikhailFirsov-1277 may want the first/last value over the entire set, in which case UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING would be appropriate.
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!
Maybe the experiments show that your understanding is incorrect. Perhaps, after ordering ‘ORDER BY quantity’, the system takes for each output row any available value from the “tie”.
LAST_VALUE is nondeterministic, meaning that it can yield a different value even with the same input values (i.e. the window). Add Product to the ORDER BY clause so that you'll get the same product in the case of ties: OVER(ORDER BY Quantity, Product).
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."
What I meant by my comment is the specified order by columns need to be unique for deterministic results.
Here's the test without ties:
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]
FROM Persentile2
Regards,
Michael
10 people are following this question.
