Search code examples
sqlsql-servert-sqlsql-order-by

DB2 into T-SQL. Alternative of NULLS LAST in T-SQL

FIRST_VALUE(B.LOC_X) OVER (PARTITION BY A.ACTIONBLOCKID ORDER BY CASE WHEN B.EVENT_TIMESTAMP <= A.ACTIONBLOCKENDTSTAMP THEN B.EVENT_TIMESTAMP END DESC NULLS LAST) AS DROPOFF_SCANLOC_X

I have this particular statement written in DB2 and since, NULLS LAST does not work in T-SQL. I am looking for a way to do the same thing in T-SQL.

Solution

  • The alternative, as I mention, would be to use ISNULL to provide an arbitrarily high value so that NULL values are last. As, however, you are using a CASE expression here, then the arbitrarily high value would be in the ELSE clause:

    FIRST_VALUE(B.LOC_X) OVER (PARTITION BY A.ACTIONBLOCKID
                           ORDER BY CASE WHEN B.EVENT_TIMESTAMP <= A.ACTIONBLOCKENDTSTAMP THEN B.EVENT_TIMESTAMP
                                         ELSE 1000
                                    END DESC) AS DROPOFF_SCANLOC_X

    I use your suggestion of 1000 here, however, considering the column is called EVENT_TIMESTAMP I suspect that this is going to give you an error, or not the date you want (1000 as a datetime is 1902-09-28 00:00:00.000, which really isn't a "high value" date). More likely you want an arbitrarily date well into the future (like '99991231').