I'm trying to show the most commented posts of my web but is being impossible .-. I just got this mysql error all the time (Can't group on 'comments')
the relation between tables is:
table: post / colums: id_post, title, id_comment
table : comment / colums: id_comment, text, id_post
and this is the query I'm trying to use
SELECT p.title AS title, COUNT(c.id_comment) AS comments
FROM post p
INNER JOIN comment c ON p.id_post=c.id_post
GROUP BY comments DESC
Please any alternative or solution for this?
Why would you want to group by on the number of comments? You need order by clause to get the most commented to the top and group by posts:
SELECT p.title AS title, COUNT(c.id_comment) AS comments
FROM post p
INNER JOIN comment c ON p.id_post=c.id_post
GROUP BY p.id_post, p.title
ORDER BY comments DESC
You may want to have a limit clause as well to get the top N commented posts only.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments