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

@Dominic Tripodi you wrote "In other window functions the ORDER BY clause does not affect the range of the window." That is incorrect. If your OVER clause has an ORDER BY, then the range has a default. The default range is from the first row in the partition (as defined by the ORDER BY clause) to the current row.

That is if you don't have a RANGE clause, the range defaults to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This is documented in the OVER clause documentation at https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver16

That's why FIRST_*VALUE gets the value in first row in the partition, but LAST_*VALUE gets the value in the current row (as defined by the ORDER BY).

Tom

Answer 2

There is no concept in SQL Server of "the row last inserted".

LAST_VALUE() looks for the last row based on the ORDER BY clause. If your ORDER BY is non-deterministic, like your ORDER BY [Year], you will get random results. It may be the same 9,999,999 times, and then on the last time, it is different. Your ORDER BY must be deterministic.

Answer 3

I think that you must write ORDER BY [DATE], because ORDER BY [YEAR] does not give a precise order of rows in your case. (Any order is possible because the YEAR is the same.

Answer 4

Already tested, but it doen't function:

Answer 5

Try this:

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