sql server - SQL: Select maximum value for each unique key?

Question

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:

SELECT *
FROM Samples
WHERE FunctionId NOT IN
(SELECT CalleeId FROM Callers)
ORDER BY ThreadId, HitCount DESC

Which gives me:

ThreadId   Function  HitCount
       1        164      6945
       1       3817         1
       4       1328      7053

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.

[EDIT] If it helps, this is an alternate form of the same query:

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

[EDIT] I ended up making schema changes to avoid doing this, as the suggested queries were looking rather expensive. Thanks for all the help.

Answer 1

Here's how I would do it:

SELECT s1.*
FROM Samples s1
LEFT JOIN Samples s2 
  ON (s1.Thread = s2.Thread and s1.HitCount < s2.HitCount)
WHERE s1.FunctionId NOT IN (SELECT CalleeId FROM Callers) 
  AND s2.Thread IS NULL
ORDER BY s1.ThreadId, s1.HitCount DESC

In other words, the row s1 for which there is no other row s2 matching the same Thread and having a greater HitCount.