SQL: select the maximum value for each unique key?

Question

Sorry, I'm not sure how to do this, and I'm really not very good at SQL. The db core is SQL Server Compact. I have this query:

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

What gives me:

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

Now I want to get the result with the maximum number of hits for each unique Thread value. In other words, this second line should be discarded. I do not know how to do that.

[EDIT] If this helps, this is an alternative form of the same request:

 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] As a result, I made schema changes to avoid this, as the proposed queries looked pretty expensive. Thanks for the help.

Answer 1

Does SQL Server support compact windowing support?

Alternative 1 - Will include all strings that bind. Will not include a line if the only lines for this Thread all are null for HitCount:

 SELECT Thread, Function, HitCount FROM (SELECT Thread, Function, HitCount, MAX(HitCount) over (PARTITION BY Thread) as MaxHitCount FROM Samples WHERE FunctionId NOT IN (SELECT CalleeId FROM Callers)) t WHERE HitCount = MaxHitCount ORDER BY ThreadId, HitCount DESC 

Alternative 2 - Will include all strings that bind. If there is no row for this stream with a nonzero HitCount, it will return all rows for this stream:

 SELECT Thread, Function, HitCount FROM (SELECT Thread, Function, HitCount, RANK() over (PARTITION BY Thread ORDER BY HitCount DESC) as R FROM Samples WHERE FunctionId NOT IN (SELECT CalleeId FROM Callers)) t WHERE R = 1 ORDER BY ThreadId, HitCount DESC 

Alternative 3 - Will not deterministically select one row in the case of links and discard others. Will include a line if all lines for this stream are null HitCount

 SELECT Thread, Function, HitCount FROM (SELECT Thread, Function, HitCount, ROW_NUMBER() over (PARTITION BY Thread ORDER BY HitCount DESC) as R FROM Samples WHERE FunctionId NOT IN (SELECT CalleeId FROM Callers)) t WHERE R = 1 ORDER BY ThreadId, HitCount DESC 

Alternative 4 and 5 - Uses old constructs if window functions are not available, and says that it means a little cleaner than using joins. Benchmark when it comes to priority. Both return all the lines that are involved in the tie. In Alternative 4, HitCount is null if non-zero values ​​are not available for HitCount. Alternative 5 will not return rows with HitCount null.

 SELECT * FROM Samples s1 WHERE FunctionId NOT IN (SELECT CalleeId FROM Callers) AND NOT EXISTS (SELECT * FROM Samples s2 WHERE s1.FunctionId = s2.FunctionId AND s1.HitCount < s2.HitCount) ORDER BY ThreadId, HitCount DESC SELECT * FROM Samples s1 WHERE FunctionId NOT IN (SELECT CalleeId FROM Callers) AND HitCount = (SELECT MAX(HitCount) FROM Samples s2 WHERE s1.FunctionId = s2.FunctionId) ORDER BY ThreadId, HitCount DESC 

Answer 2

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, line s1 , for which there are no other lines s2 , corresponding to the same Thread and having a larger HitCount .

Answer 3

Will work with SQL Server 2005 +:

 WITH maxHits AS( SELECT s.threadid, MAX(s.hitcount) 'maxhits' FROM SAMPLES s JOIN CALLERS c ON c.threadid = s.threadid AND c.calleeid != s.functionid GROUP BY s.threadid ) SELECT t.* FROM SAMPLES t JOIN CALLERS c ON c.threadid = t.threadid AND c.calleeid != t.functionid JOIN maxHits mh ON mh.threadid = t.threadid AND mh.maxhits = t.hitcount 

Work with any database:

 SELECT t.* FROM SAMPLES t JOIN CALLERS c ON c.threadid = t.threadid AND c.calleeid != t.functionid JOIN (SELECT s.threadid, MAX(s.hitcount) 'maxhits' FROM SAMPLES s JOIN CALLERS c ON c.threadid = s.threadid AND c.calleeid != s.functionid GROUP BY s.threadid) mh ON mh.threadid = t.threadid AND mh.maxhits = t.hitcount