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

Answer 2

select c.id, count(cmt.*) as cnt
    from  Content c, Comment cmt
where c.id = cmt.id
order by cnt
group by c.id,

Answer 3

Let's assume your tables look like this (I wrote this in pseudo-SQL - syntax may differ depending on database you are using). From the description you provided, it is not clear how you are joining the tables. Nevertheless, I think it looks something like this (with the caveat that all primary keys, indexes, and so forth are missing):

CREATE TABLE [dbo].[Content] (
    [ContentID] [int] NOT NULL,
    [ContentText] [varchar](50) NOT NULL
)

CREATE TABLE [dbo].[ContentComments] (
    [ContentCommentID] [int] NOT NULL,
    [ContentCommentText] [varchar](50) NOT NULL,
    [ContentID] [int] NOT NULL
)

ALTER TABLE [dbo].[ContentComments]  WITH CHECK ADD  CONSTRAINT
[FK_ContentComments_Content] FOREIGN KEY([ContentID])
REFERENCES [dbo].[Content] ([ContentID])

Here is how you would write your query to get the content sorted by the number of comments each piece of content has. The DESC sorts the content items from those with the most comments to those with the least comments.

SELECT Content.ContentID, COUNT(ContentComments.ContentCommentID) AS CommentCount
FROM Content
INNER JOIN ContentComments
ON Content.ContentID = ContentComments.ContentID
GROUP BY Content.ContentID
ORDER BY COUNT(ContentComments.ContentCommentID) DESC