question

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 asked MikhailFirsov-1277 commented

Question on Window Function 2

Hello,

Help me please with this:

79550-q-fv-lv.png



Thank you in advance,
Michael

sql-server-transact-sql
q-fv-lv.png (129.2 KiB)
5 |1600 characters needed characters left characters exceeded

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

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered MikhailFirsov-1277 commented

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
5 |1600 characters needed characters left characters exceeded

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 ·
APoblacion avatar image
0 Votes"
APoblacion answered

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.

5 |1600 characters needed characters left characters exceeded

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

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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.

5 |1600 characters needed characters left characters exceeded

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

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered DanGuzman commented

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
5 |1600 characters needed characters left characters exceeded

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 ·
MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered DanGuzman commented

I've already tried to get rid of ties before posting:
79649-q-fv-lv2.png


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

79686-q-fv-lv3.png



q-fv-lv.png (129.2 KiB)
q-fv-lv3.png (72.3 KiB)
q-fv-lv2.png (97.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

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 ·
MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered

Here's the test without ties:

79695-q-fv-lv4.png



q-fv-lv4.png (165.5 KiB)
5 |1600 characters needed characters left characters exceeded

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

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered

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

80193-q-fv-lv5.png

Regards,
Michael



q-fv-lv5.png (40.8 KiB)
5 |1600 characters needed characters left characters exceeded

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