CODEWITHSUNDEEP
sqlsql-serverwindow-functions
nonslearn
nonslearn

Reputation: 29

SQL Server -- Window Function can't use " Between xx and xx"

I have a window function to identify the latest row for each member that can work in 3 out of 4 servers, but there is 1 server that can't use that. May that server structure old?

I want to know if there is a solution for that, code below:

    CASE 
        WHEN EFF_DT = first_value(EFF_DT) over
                                (partition by Member_CK  
                                order by EFF_DT desc
                                **rows between unbounded preceding and current row**)
             OR (Member_CK  IS NULL AND Identity_CK IS NULL)
        then 'Y'
        else 'N'
    END AS [LATEST_ROW]

Try to emphasis inside the code but not work, the bug happens at the 3rd row:

rows between unbounded preceding and current row

Error Messages:

Incorrect syntax near 'rows'.

UPDATE: Appreciated the tip, I can verify the server version is SQL SERVER 2008(SP2), other 3 servers are in 2014 or 2017 version.

Thanks to @Gordon Linoff pointed out the rows line can be delete, but then I will get another error message:

'first_value' is not a recognized built-in function name.

Upvotes: 0

Views: 109

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271201

Remove rows. It is not needed:

first_value(EFF_DT) over (partition by Member_CK  
                          order by EFF_DT desc
                         )

Upvotes: 1

Related Questions