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

Here's the test without ties:

Answer 3

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