title: SQL Server group by but select 'top' date
body: <p>I have a table in SQL server like so (Note the ID field is not unique):</p>
  
  <pre><code>-----------------------------------
  | ID | IsAdamBrown | DateComplete |
  | 1  |     TRUE    | 2017-01-01   |
  | 1  |     TRUE    | 2017-01-03   |
  -----------------------------------
  </code></pre>
  
  <p>I'd like to select one row for all the unique IDs in the table and the <strong><em>most recent</em></strong> 'DateComplete' for that ID.</p>
  
  <p>My desired output in this case would be:</p>
  
  <pre><code>-----------------------------------
  | ID | IsAdamBrown | DateComplete |
  | 1  |     TRUE    | 2017-01-03   |
  -----------------------------------
  </code></pre>
  
  <p>I've tried:</p>
  
  <pre><code>  SELECT DISTINCT DateComplete, ID, IsAdamBrown 
    FROM thisTable
    WHERE IsAdamBrown IS NOT NULL
    GROUP BY DateComplete, ID, IsAdamBrown
    ORDER BY DateComplete DESC
  </code></pre>
  
  <p>Unfortunately I still get the two date rows back. In MySQL I would group by just the first two rows and the <code>ORDER BY</code> would make sure the DateComplete was the most recent. SQL servers requirement that the <code>SELECT</code> fields match the <code>GROUP BY</code> makes this impossible.</p>
  
  <p><strong><em>How can I get a single row back for each ID with the most recent DateComplete?</em></strong></p>