Order by Count of matching entries

[HowardMarks]

Hi Guys I wonder if you may be able to help.

I wish to select a group of CATEGORIES, and order them by the number of POSTS which they contain. This is to result in a list of 'most popular categories'.

So basically the SQL I need to use needs to be something like:

"SELECT * FROM CATEGORIES ORDER BY Count(SELECT * FROM POSTS WHERE POSTS.POST_CATEGORYID = CATEGORIES.CATEGORY_ID)"

But MS Access doesn't seem to like this use of Count().
Would any one have any ideas how to go about ordering the categories in this way?

Many thanks

Nick

Nick (Webmaster)

http://www.npfx.com

http://www.artswales.org

 

[DotNetGnat]

try something like this:

Select A.CatId, A.CatName, B.CatCount from CATEGORIES A INNER JOIN
(Select CatId, Count(*) as CatCount from POSTS groupd by CatId) B
ON A.CatId= B.CatId

-DNG

 

[DotNetGnat]

oops...missed the order by clause

Select A.CatId, A.CatName, B.CatCount from CATEGORIES A INNER JOIN
(Select CatId, Count(*) as CatCount from POSTS groupd by CatId) B
ON A.CatId= B.CatId
order by 3 desc

-DNG

 

[Tarwn]

Or maybe even:

SELECT C.Category_ID, C.Category_Name, COUNT(P.Post_ID) as PostCount
FROM Category C LEFT JOIN Posts P ON C.Category_ID = P.Category_ID
GROUP BY C.Category_ID, C.Category_Name
ORDER BY COUNT(P.Post_ID) DESC

-T

 

[HowardMarks]

Thanks both for your help. Tarwn, your code did the trick perfectly.

Thanks again
Nick

Nick (Webmaster)

http://www.npfx.com

http://www.artswales.org