MySQL - Select statement, order by number of comments per article.

Question

Hi I'd like to do something like the following:

SELECT * FROM tbl_article
JOIN tbl_comments ON tbl_article.id = tbl_comments.article_id
ORDER BY COUNT(tbl_comments.article_id)

Can anyone suggest how I might get this to work?

Answer 1

This should do the job:

SELECT 
    tbl_article.*, COUNT(tbl_comments.article_id) as total_comments
FROM 
    tbl_article
LEFT JOIN 
    tbl_comments ON tbl_comments.article_id = tbl_article.id
GROUP BY 
    tbl_article.id
ORDER BY 
    COUNT(tbl_comments.article_id)

Answer 2

SELECT * FROM
(
  SELECT tbl_article.id, COUNT(tbl_comments.id) AS CommentCount FROM tbl_article
  LEFT OUTER JOIN tbl_comments ON tbl_article.id = tbl_comments.article_id
  GROUP BY tbl_article.id
) ra
ORDER BY CommentCount DESC

EDIT : I have changed the join type. Articles should appear regardless of whether they have corresponding entries in the comments table. We're also now grouping on the id field in the articles table.