Question on Window Function 2

Question

Hello,

Help me please with this:

Thank you in advance,
Michael

Answer 1

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')

`

Answer 2

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.

Answer 3

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.

Answer 4

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!

Answer 5

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