title: SQL Grouping and table values
body: <p>I am trying to sort out for the website for my game,
  The scores are stored in SQL.
  The game stores everyones event scores, so naturally there are multiples for each player.
  I am trying to gather the top for each player.
  At the moment I am using a grouping </p>
  
  <pre><code>SELECT TOP(250) * 
    FROM
         ( SELECT CharName, CharType, MAX(Point) AS SCORE
             FROM SOD2RecBySandurr
         GROUP BY CharName, CharType) AS derivedtbl_1 
            WHERE CharName 
              NOT LIKE '\[GM\]%' ESCAPE '\' 
             AND SCORE NOT LIKE '0' 
         ORDER BY SCORE DESC
  </code></pre>
  
  <p>This works fine, But I want to add kill count to the information I am displaying on the website.
  I tried adding KillCount to the group by part but it then shows everyones scores as naturally the kill count differs on each attempt.</p>
  
  <pre><code>SELECT TOP(250) * 
    FROM
                  (SELECT CharName, CharType, KillCount, MAX(Point) AS SCORE
                     FROM SOD2RecBySandurr
                 GROUP BY CharName, CharType, KillCount) AS derivedtbl_1 
                    WHERE CharName 
                      NOT LIKE '\[GM\]%' ESCAPE '\' 
                      AND SCORE NOT LIKE '0' 
                 ORDER BY SCORE DESC
  </code></pre>
  
  <p>How can I make it do the character grouping based on charname but display the chartype and killcount, 
  if i have killcount in the select but not in the group by then it throws back an error.</p>
  
  <p>This one has me baffled :/ I understand why its not grouping them based on name (given the killcount wont match any other entries) but I just cant get my head around why it requires it to be in the group by for the select function.</p>
  
  <p>EDIT:</p>
  
  <p>Present results are:  </p>
  
  <pre><code>Player A - SCORE 1 - Kills 1
  Player A - SCORE 2 - Kills 2
  Player A - SCORE 3 - Kills 3
  Player B - SCORE 1 - Kills 1
  </code></pre>
  
  <p>Where I want to see </p>
  
  <pre><code>Player A - Score 1 - Kills 1 (Be that the max score)
  Player B - Score 1 - Kills 1
  </code></pre>
  
  <p>I want to sort by max score but include the kill count for that score</p>