title: MAX with extra criteria including group by
body: <p>Following on from this question (<a href="https://stackoverflow.com/questions/18327082/max-with-extra-criteria">MAX with extra criteria</a>), I've been told I need to open a new question for further information.</p>
  
  <p>The above question relates to getting the highest score for a player and if the highest score has a value of "not out" within the "BatHowOut" field, it should show as 96* rather than 96.</p>
  
  <p>I need to be able to get the highest score also by grouping by PlayerID which is what this question is about. </p>
  
  <pre><code>SELECT
     PlayerID,
     MAX(CAST(MatchPlayerBatting.BatRuns AS SIGNED)) AS HighestScore
  FROM
     MatchPlayerBatting
  GROUP BY
     PlayerID
  </code></pre>
  
  <p>As per the previous question, please consider:</p>
  
  <pre><code>BatRuns   BatHowOut
  96        not out
  96        lbw
  
  BatRuns   BatHowOut
  96        not out
  102       lbw
  </code></pre>