Using TOP 5 with COUNT and LEFT JOIN

Question

Hi folks,

I've been using the following code, given to me by HansUp (cheers!), and it's been working great:

SELECT g.ID, Count(t.Grade) AS Total
FROM grade AS g 
LEFT JOIN (SELECT Grade FROM telephony WHERE [Date] BETWEEN #08/16/2010# AND #08/20/2010#) AS t ON g.ID=t.Grade 
GROUP BY g.ID 
ORDER BY 2 DESC; 

I'm now looking to find the TOP 5 results returned. I thought it would be as simple as:

SELECT **TOP 5** g.ID, Count(t.Grade) AS Total
FROM grade AS g 
LEFT JOIN (SELECT Grade FROM telephony WHERE [Date] BETWEEN #08/16/2010# AND #08/20/2010#) AS t ON g.ID=t.Grade 
GROUP BY g.ID 
ORDER BY 2 DESC; 

Unfortunately that's not working.

Does anyone have any ideas.

Thanks

Answer 1

This should work.

SELECT TOP 5 g.ID, Count(t.Grade) AS Total
FROM grade AS g 
LEFT JOIN (SELECT Grade FROM telephony WHERE [Date] BETWEEN #08/16/2010# AND #08/20/2010#) AS t ON g.ID=t.Grade 
GROUP BY g.ID 
ORDER BY 2 DESC

Answer 2

The TOP clause will get you the top based on your first sort field. Since your first sort field is a constant (2) for all records, you get all records. Add the ID field to your ORDER BY clause and you'll only get five records.

SELECT TOP 5 g.ID, Count(t.Grade) AS Total
FROM grade AS g LEFT JOIN (SELECT Grade FROM telephony WHERE [Date] BETWEEN #08/16/2010# AND #08/20/2010#)  AS t ON g.ID = t.Grade
GROUP BY g.ID
ORDER BY g.ID, 2 DESC;

If you're actually after the top 5 by Total in descending order, change the SQL to the following:

SELECT TOP 5 g.ID, Count(t.Grade) AS Total
FROM grade AS g LEFT JOIN (SELECT Grade FROM telephony WHERE [Date] BETWEEN #08/16/2010# AND #08/20/2010#)  AS t ON g.ID = t.Grade
GROUP BY g.ID
ORDER BY Count(t.Grade) DESC , 2 DESC;

This is top by value, so if multiple records have a total that is the same and it happens to be in the top 5 value of Total, you'll get them all back. If you truly only ever want five records back, you have to sort on a field that is unique.