title: SQL Server Select the most recent past date if no future date available
body: <p>I have a table structure as below,</p>
  
  <pre><code>CREATE TABLE #CustOrder ( CustId INT, OrderDate DATE )
  
  INSERT #CustOrder ( CustId, OrderDate )
  VALUES  ( 1, '2016-11-01' ),
          ( 1, '2019-09-01' ),
          ( 2, '2019-07-01' ),
          ( 2, '2019-11-01' ),
          ( 3, '2017-01-01' ),
          ( 4, '2016-12-01' ),
          ( 4, '2017-01-01' )
  </code></pre>
  
  <p>I want to list the customer with their future order dates, if they do not have a future order I want to list their last or most recent order. I have the following query.</p>
  
  <pre><code>; WITH LastOrder AS
  (
      SELECT 
          CO.CustId, 
          CO.OrderDate, 
          ROW_NUMBER() OVER(PARTITION BY CO.CustId ORDER BY ABS(DATEDIFF(DAY, CO.OrderDate, GETUTCDATE()))) AS RowNum 
      FROM #CustOrder AS CO
  
  )
  SELECT LO.CustId, LO.OrderDate
  FROM LastOrder AS LO
  WHERE LO.RowNum = 1
  </code></pre>
  
  <p>This query gives me the result as,</p>
  
  <pre><code>CustId  |   OrderDate
  --------+-------------
  1       |   2016-11-01
  2       |   2019-07-01
  3       |   2017-01-01
  4       |   2017-01-01
  </code></pre>
  
  <p>However, I need the result as, </p>
  
  <pre><code>CustId  |   OrderDate
  --------+-------------
  1       |   2019-09-01
  2       |   2019-07-01
  3       |   2017-01-01
  4       |   2017-01-01
  </code></pre>
  
  <p>As </p>
  
  <ol>
  <li>Customer 1 has a future order on 2019-09-01</li>
  <li>Customer 2 has two future order but the first one is on 2019-07-01</li>
  <li>Customer 3 has no more than 1 order, it should just return 2017-01-01</li>
  <li>Customer 4 has two past orders but the most recent is 2017-01-01</li>
  </ol>