# question

## Question on Window Function 2

Hello,

Help me please with this:

Thank you in advance,
Michael

q-fv-lv.png (129.2 KiB)

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Vote"

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

`

· 2

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

DanGuzman, exactly!!! Thank you very much!

0 Votes 0 ·

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.

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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!

· 2

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

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

0 Votes 0 ·

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

q-fv-lv.png (129.2 KiB)
q-fv-lv3.png (72.3 KiB)
q-fv-lv2.png (97.5 KiB)
· 1

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What I meant by my comment is the specified order by columns need to be unique for deterministic results.

0 Votes 0 ·

Here's the test without ties:

q-fv-lv4.png (165.5 KiB)

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

q-fv-lv5.png (40.8 KiB)

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.