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

Question

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.

What is @@VERSION on the one where you get the error? And what compatibility level is the DB set to? — Martin Smith
please attach full query or a sample data, so we could help you on that — Jiacheng Gao
@MartinSmith Thanks for letting me know that tip, I can see 3 out of 4 are SQL Server 2017 but the one not work is SQL Server 2008(SP2) — nonslearn
@JiachengGao Thanks a lot, can consider this is as simple as select 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], Member_ID, Member_Name, EFF_DT from Membership — nonslearn

Gordon Linoff Gordon Linoff · Accepted Answer · 2021-01-25T16:05:24

Remove rows. It is not needed:

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

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

1 Answers