title: Tsql - get entire row information with max and group by
body: <p>I have a table, with the following columns:</p>
  
  <pre><code>PK1   PK2   ID   DATE    Value  flag
  </code></pre>
  
  <p>I do a calculation that involves taking the max value per ID. </p>
  
  <pre><code>  select id,
           max(value) 
      from table 
  group by id
  </code></pre>
  
  <p>I want to mark the flag on the rows that I am using. If id and the max(value) correspond to multiple rows flag the one with the max date. If they have the same id,max(value) and max(date) flag exactly one of those rows (don't care which at that point)</p>
  
  <p>Any ideas?</p>
  
  <p>Thanks!</p>