SQL: row_number: order by date asc Need Nulls to be Last

Question

Since my DBMS doesn't allow for 'Nulls Last' in an order by clause, I need help with the following.

row_number() over(Partition by a.ID order by a.Date asc

I need my rows to have a row number sequence by ID ordered by date ascending, but have the null date rows be LAST in my sequence. Obviously it'd be great if I could just say "NULLS LAST" in my order by, however, my dbms (MSSQL) doesn't allow for that.

FOR EXAMPLE what is currently happening:

ID   Date     ROW_NUMBER
1    NULL      1
1    1/2/17    2
1    1/3/17    3
2    NULL      1
2    2/2/17    2
2    2/3/17    3
2    2/4/17    4

what I WANT to happen:

ID   Date     ROW_NUMBER
1    1/2/17    1
1    1/3/17    2
1    NULL      3
2    2/2/17    1
2    2/3/17    2
2    2/4/17    3
2    NULL      4

Help? THANK YOU!

Answer 1

Just add another key to the order by:

row_number() over (Partition by a.ID
                   order by (case when a.date is not null then 1 else 2 end),
                            a.Date asc
                  )