Understanding the LAST_VALUE() behaviour - SQL Server 2019

Question

Hi,
I'm trying to use the LAST_VALUE() OVER(...) function but I cannot understand a strange behaviour.
For this query I've obtained an unexpected result:

SELECT *,  
	LAST_VALUE(VALUE) OVER(PARTITION BY [YEAR] ORDER BY [YEAR]) as LAST_VALUE  
FROM Tab  
ORDER BY [DATE]  

I'm waiting for the 14 value.

As the last row I've inserted the 2021-05-25 date.

/****** Object:  Table [dbo].[Tab]    Script Date: 15/10/2021 17:21:18 ******/  
SET ANSI_NULLS ON  
GO  
  
SET QUOTED_IDENTIFIER ON  
GO  
  
CREATE TABLE [dbo].[Tab](  
	[YEAR] [smallint] NULL,  
	[DATE] [date] NULL,  
	[VALUE] [int] NULL  
) ON [PRIMARY]  
GO  
  
  

Now, any helps to me, please? Many thanks

Answer 1

Hey All, I can't find a good explanation of this either, and I feel like no one is really understanding the OPs question. "Why does the field in the ORDER BY clause get applied to the partition when we don't include RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"

I am also trying to understand why this is happening.

If we remove the ORDER BY it almost works as expected, because the PARTITION creates the range for the window function, but then if the result set is in a different order, the "last" value will be different because the order is different.

In other window functions the ORDER BY clause does not affect the range of the window. If we compare FIRST_VALUE as an example, we can see that it works as expected without adding "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"

Does anyone know why LAST_VALUE includes the ORDER BY in the partition, or what is actually happening if it is not including ORDER BY in the partition?

Thanks,
Dominic