Need to find number of comments on most commented post: SQL-CodePudding

I have two tables and i need to find the number of comments on a most commented post.

I can get all post ordered with a number of comments but I want to add that to subquery.

My code at the moment:

SELECT TOP 1 p.PostID, COUNT(*) AS num_comments
FROM Comment p
GROUP BY p.PostID
ORDER BY num_comments DESC

But then I have a one column with PostID as well and I don't want to put that on table below. How can i get only the value of mostCommentsPerPost ?

Sorry If i dont explain my problem well enough, this is my first post.

Thanks!

CodePudding user response：

just take out p.PostID from SELECT

SELECT TOP 1 COUNT(*) AS num_comments
FROM Comment p
GROUP BY p.PostID
ORDER BY num_comments DESC

CodePudding user response：

I think you need something like this

SELECT sq.postId, sq.Cnt
FROM (
    SELECT PostId, count(*) AS Cnt
    FROM Comment
    GROUP BY PostId
) AS sq
ORDER BY sq.Cnt DESC
LIMIT 1