title: Select MAX value and cast
body: <p>I have a table with the following columns:</p>
  
  <pre><code>[Id]
  [fk_Player_Id]
  [fk_Innings_Id]
  [Minutes]
  [Balls]
  [Runs]
  [fk_HowOut_Id]
  [fk_Bowler_Id]
  </code></pre>
  
  <p>I need a query that returns the record with the maximum runs but based on the fk_HowOut_Id value for the row i might need to add an asterisk to the value.</p>
  
  <p>I have tried this query - </p>
  
  <pre><code>SELECT Player.LastName + ' ' + LEFT(player.FirstName, 1) as PlayerName,COUNT(DISTINCT match.MatchId) AS Matches, 
  COUNT(CASE WHEN BatInnings.fk_HowOut_Id != 1 THEN 1 ELSE NULL END) AS 'Innings',
  COUNT(CASE WHEN BatInnings.fk_HowOut_Id = 7 THEN 1 ELSE NULL END) AS 'Not Outs',
  SUM(BatInnings.Runs) AS 'Aggregate',
  CASE WHEN (COUNT(CASE WHEN BatInnings.fk_HowOut_Id != 1 THEN 1 ELSE NULL END) - COUNT(CASE WHEN BatInnings.fk_HowOut_Id = 7 THEN 1 ELSE NULL END)) &gt; 0 THEN SUM(BatInnings.Runs) / (COUNT(CASE WHEN BatInnings.fk_HowOut_Id != 1 THEN 1 ELSE NULL END) - COUNT(CASE WHEN BatInnings.fk_HowOut_Id = 7 THEN 1 ELSE NULL END)) ELSE 0 END AS 'Average',
  CAST(MAX(BatInnings.Runs) AS varchar(3)) + CASE WHEN BatInnings.fk_HowOut_Id = 7 THEN '*' 
                                                                                   ELSE ''
                                                                                   END AS 'High Score'
  FROM Match
  JOIN Innings ON Innings.fk_Match_Id = Match.MatchId
  JOIN BatInnings ON BatInnings.fk_Innings_Id = Innings.Id
  Join Player On player.Id = BatInnings.fk_Player_Id
  WHERE Player.ClubId = 1088
  group by Player.id, player.FirstName, player.LastName
  Order by PlayerName
  </code></pre>
  
  <p>and i get an error message returned of: </p>
  
  <pre><code>Column 'BatInnings.fk_HowOut_Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
  </code></pre>
  
  <p>Can i get pointed in the right direction.</p>
  
  <p>Thanks..</p>