CODEWITHSUNDEEP
mysqlsql
Sam
Sam

Reputation: 13

mySQL finding the top 3 posts depending on the number of comments on them

In my database there are 2 tables relating to posts the users make: tblPosts - which contains the information about a post a user has made. And tblComments which contains the comments made by users.

tblPosts:

has this structure

tblComments:

has this structure

My goal is to select the top 3 most popular posts, these are the 3 posts with the most comments on them.

I am however stumped on how to do this and any help would be greatly appreciated!

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can use aggregation, order by, and limit:

select post_id, count(*) as num_comments
from comments c
group by post_id
order by count(*) desc
limit 3;

Upvotes: 1

Related Questions