SQL function to sort by most popular content

Question

I don’t know if this is possible using SQL: I have two tables, one of which contains an integer identifier and a comment table, each of which has an “On” field indicating the content on which it is included. I would like to get the content in the order of the number of comments in the “On” field and was hoping SQL would 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 content fields, you can make a connection:

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

, ( -SQL-, ). , . , , ( , , .. ):

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's how you could write your request to get content sorted by the number of comments each piece of content has. DESC sorts content items from those who have more comments to those who have less 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