CODEWITHSUNDEEP
ms-accessms-access-2003
Richard L
Richard L

Reputation: 21

Using TOP 5 with COUNT and LEFT JOIN

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

Upvotes: 0

Views: 1386

Answers (3)

David-W-Fenton
David-W-Fenton

Reputation: 23067

So far as I can see, 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 Count(t.Grade) DESC;

The key point here is that you use the full expression from the SELECT statement when you want to use it in a WHERE or ORDER BY clause.

If you'd just use the Access query grid to write your SQL, you would have gotten the correct results right off the bat (though you'd have to dip into SQL view to write your subquery).

Upvotes: 0

Simon Kingston
Simon Kingston

Reputation: 495

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.

Upvotes: 3

Damian Leszczyński - Vash
Damian Leszczyński - Vash

Reputation: 30875

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

Upvotes: 0

Related Questions