SQL function to sort by most popular content

Question

I don't know if this is possible with SQL: I have two tables, one of content, each with an integer ID, and a table of comments each with an "On" field denoting the content it is on. I'd like to receive the content in order of how many comments have it in their "On" field, and was hoping SQL could do it.

Answer 1

SELECT   comment.on AS content_id, COUNT(comment_id) AS num_comments
FROM     comments
GROUP BY content_id
ORDER BY num_comments DESC

If you need all the fields of the content, you can do a join:

SELECT   contents.*, COUNT(comment_id) AS num_comments
FROM     contents
  LEFT JOIN comments on contents.content_id = comments.on
GROUP BY content_id
ORDER BY num_comments DESC