title: SQL: row_number: order by date asc Need Nulls to be Last
body: <p>Since my DBMS doesn't allow for 'Nulls Last' in an order by clause, I need help with the following.</p>
  
  <p>row_number() over(Partition by a.ID order by a.Date asc</p>
  
  <p>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.</p>
  
  <p>FOR EXAMPLE what is currently happening:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>what I WANT to happen:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Help? THANK YOU!</p>