Last_Value does not return expected values - by eralper

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 679668 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 7/17/2011 9:05:37 AM
Access Restriction Public

Description

Hello,
While I was testing new functions, while First_Value() is working as expected I see that Last_Value() function does not return expected values
Sign in to post a comment.
Posted by eralper on 7/18/2011 at 3:12 PM
Thanks a lot Umachandar, I missed the importance of rows_range_clause
Posted by Umachandar [MSFT] on 7/18/2011 at 11:58 AM
Hello,
Thanks for your feedback. The behavior you are seeing is by design and according to the ANSI SQL specification. Basically the rule is that if window frame is not specified then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW or RANGE UNBOUNDED PRECEDING. So in case of LAST_VALUE, you will always get CURRENT ROW if the frame is omitted. To get the expected results, you need to specify the entire frame. See below for examples:

select Id
     , val
     , Last_Value(val) OVER (Order By Id) as LV1_CURRENT_ROW
     , Last_Value(val) OVER (Order By Id range unbounded preceding) as LV2_CURRENT_ROW
     , Last_Value(val) OVER (Order By Id range between unbounded preceding and current row) as LV3_CURRENT_ROW
     , Last_Value(val) OVER (Order By Id rows between unbounded preceding and unbounded following) as LV3_LAST_ROW_BY_ROWS
     , Last_Value(val) OVER (Order By Id range between unbounded preceding and unbounded following) as LV3_LAST_ROW_BY_RANGE
from (Values (1, 1),(2, 2),(3, 3), (4, 4) ) as t(Id, val)


--
Umachandar, SQL Programmability Team