SELECTing most popular subcategories

Hi,

To get the most popular subcategories in each category, is there any way I can calculate it using SQL's COUNT() function, or should I include a link_count attribute along with each category, update it every time I add a link and then sort by that?

The calculated method works, but it will only return populated subcategories, while the other would require more maintainence, making sure the count is up to date.

For the calculated method, I've tried the following SQL, which is no good since it only returns the populated categories:

SELECT `categories`.`id`, `categories`.`parent_id`, `categories`.`title`, `categories`.`description`, COUNT(`links`.`id`) as 'link_count'
FROM `categories`, `links`
WHERE `categories`.`parent_id` = [b]$id[/b]
AND `links`.`category_id` = `categories`.`id`
GROUP BY `categories`.`id`
ORDER BY 'link_count' DESC
LIMIT 0,3

Code (markup):

Any ideas how to maybe return the most popular, and if they're empty, return them in alphabetical order?

If that doesn't make sense, I'll try and explain in more detail.

 

You try first option but all above i do not think it will make much difference

 

That's the problem, the first option doesn't work properly. If there are no links in any of the subcategories ( COUNT(`links`.`id`) == 0 ), no subcategories are returned. I need 3 subcategories returned, links or not.