SQL Server 2022: Latest Update CU6 - Why does Last_Value does not work when there is no Partition?

Question

Can anyone explain why Last_Value does not take the last value (which is last as per order by) for all rows ?

select * from mytableone

(Please click on link)

Query Used:

select t.*,

LAST_VALUE(PAST_DUE_COL) OVER (Order by ID) as New_Due_Col

from

mytableone t

Actual Result (Please click on link)

Expecting that all 4 rows of result column-3 should have same value -

'0-30 days past due'

Can someone explain the reason ?

FYI, images attract downvotes. Include data in the form of a formatted table or INSERT statements. — Dan Guzman, Aug 03 '23 at 14:08

score 1 · Answer 1 · edited Aug 03 '23 at 22:29

When ORDER BY is specified, the default window frame is UNBOUNDED PRECEDING AND CURRENT ROW. Your query is functionally identical to:

SELECT t.*, LAST_VALUE(PAST_DUE_COL) OVER (Order by ID RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as New_Due_Col
FROM mytableone t;

so LAST_VALUE will be the current row value.

Specify UNBOUNDED FOLLOWING to get the LAST_VALUE over the entire result:

SELECT t.*, LAST_VALUE(PAST_DUE_COL) OVER (Order by ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as New_Due_Col
FROM mytableone t;

1 Answers1