title: SQL select max(date) and corresponding value
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/376518/how-to-get-the-record-of-a-table-who-contains-the-maximum-value">How to get the record of a table who contains the maximum value?</a>  </p>
  </blockquote>
  
  
  
  <p>I've got an aggregate query like the following:</p>
  
  <pre><code>SELECT TrainingID, Max(CompletedDate) as CompletedDate, Max(Notes) as Notes     --This will only return the longest notes entry
  FROM HR_EmployeeTrainings ET
  WHERE (ET.AvantiRecID IS NULL OR ET.AvantiRecID = @avantiRecID)
  GROUP BY AvantiRecID, TrainingID            
  </code></pre>
  
  <p>Which is working, and returns correct data most of the time, but I noticed a problem.  The Notes field which gets returned will not necessarily match the record that the max(completedDate) is from.  Instead it will be the one with the longest string?  Or the one with the highest ASCII value?  What does SQL Server do in the event of a tie between two records?  I'm not even sure.  What I want to get is the notes field from the max(completedDate) record.  How should I got about doing this?</p>