Error "The function LAST_VALUE must have an OVER clause" in SQL Server 2019 but not in Azure SQL

Question

Hi,

When I run this statement in SQL Server 2019 (CU 17, latest) I get an error. However it runs fine in Azure SQL database.
The build in Visual Studio as well as the build in Azure DevOps also throw an error. I want to deploy my local project to Azure.

ERROR: "The function 'LAST_VALUE' must have an OVER clause."

 SELECT   
 event,   
 LAST_VALUE(posttime) ignore nulls   
 OVER(partition by event order by posttime) as test  
 FROM [AdventureWorks2019].[dbo].[DatabaseLog]  

(don't bother about the useless data this returns, it's just meant as a syntax example)

It seems to have a problem with the "ignore nulls" part. If I leave that out, it works fine.

This feels like a bug to me... Or am I doing something wrong?
Is there a fix?

Answer 1

Hi @Edgar Walther (ITsmart)

This feels like a bug to me...

As experts answered, this is not a bug, just because The implementation of FIRST_VALUE and LAST_VALUE does not support IGNORE NULLs mode in SQLServer 2019.

When your data_table has intermixed NULL values, here are some alternative methods to achieve the same effect as IGNORE NULLS.

1. You could filter out NULLs in a subquery and LAST_VALUE will return the last non-null value like we want. Then join that result set back to the original data table.Like this: SELECT d.event,d2.LastNotNullValue
   FROM [AdventureWorks2019].[dbo].[DatabaseLog] d
   INNER JOIN ( SELECT DISTINCT [event], FIRST_VALUE(posttime) OVER (PARTITION BY [event] ORDER BY posttime) as LastNotNullValue
   FROM [AdventureWorks2019].[dbo].[DatabaseLog]
   WHERE posttime IS NOT NULL
   ) d2
   ON d.[event] = d2.[event]

2)Apply COUNT over partition over current row to end of the group bottom will be able to identify the last non null value within the group.

SELECT event  
      ,MAX(CASE WHEN ValuesAhead = 1 THEN Posttime END) OVER (PARTITION BY Event)  AS Last  
FROM  
(  
SELECT *,  
  LAST_VALUE(OutputValue) OVER (PARTITION BY Event ORDER BY Posttime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastValue,  
  COUNT(CASE WHEN OutputValue IS NOT NULL THEN OutputValue END) OVER (PARTITION BY Event ORDER BY Posttime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS ValuesAhead  
FROM[AdventureWorks2019].[dbo].[DatabaseLog]  
)t  

Refer to this article for more details: Simulating IGNORE NULLs Functionality On FIRST_VALUE, LAST_VALUE Functions

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
email-notifications

Answer 2

Ignore Nulls is only available with the next release (presumably will be named SQL 2022). It is not available in the current SQL 2019 release. See last-value-transact-sql

Answer 3

"Azure SQL" is not "SQL Server". They are similar, but they are not the same, not 100% compatible and have some differences.

https://learn.microsoft.com/en-us/sql/t-sql/functions/last-value-transact-sql?view=sql-server-ver16#-ignore-nulls--respect-nulls-

[ IGNORE NULLS | RESPECT NULLS ]
Applies to: SQL Server (starting with SQL Server 2022 (16.x) Preview), Azure SQL Edge

Answer 4

Thanks all for your answers. I understand the issue and the provided scripts do indeed solve this.