title: SQL: Select maximum value for each unique key?
body: <p>Sorry, I'm not sure how to phrase that and I'm really not very good with SQL. The db engine i SQL Server Compact. I currently have this query:</p>
  
  <pre><code>SELECT *
  FROM Samples
  WHERE FunctionId NOT IN
  (SELECT CalleeId FROM Callers)
  ORDER BY ThreadId, HitCount DESC
  </code></pre>
  
  <p>Which gives me:</p>
  
  <pre><code>ThreadId   Function  HitCount
         1        164      6945
         1       3817         1
         4       1328      7053
  </code></pre>
  
  <p>Now, I only want the result with the maximum hit count for each unique value of Thread. In other words, that second row should be dropped. I'm not sure how to pull this off.</p>
  
  <p>[EDIT] If it helps, this is an alternate form of the same query:</p>
  
  <pre><code>SELECT *
  FROM Samples s1
  LEFT OUTER JOIN Callers c1
      ON s1.ThreadId = c1.ThreadId AND s1.FunctionId = c1.CalleeId
  WHERE c1.ThreadId IS NULL
  ORDER BY ThreadId
  </code></pre>
  
  <p>[EDIT] I ended up making schema changes to avoid doing this, as the suggested queries were looking rather expensive. Thanks for all the help.</p>