MySQL query order the results in GROUP BY 3 Tables Count
I'm coding a listing system and I'm trying to get the posts ORDER by number of comments and votes FROM 2 tables.
Table1 : Lists => id, title, detail
Table2 : Votes => voteid, listid
Table3 : Comments => commentid, listid
WHERE MY Current query is
$q = mysql_query("SELECT * FROM zoo_leads
LEFT JOIN Votes ON Lists.id=Votes.listid
LEFT JOIN Comments ON Lists.id=Comments.listid
GROUP BY Lists.id ORDER BY Comments.listid DESC LIMIT 10
it is showing me results perfectly but ORDER BY is Lists.id Instead of nu开发者_StackOverflow社区mber of votes and comments
Try:
SELECT *
FROM zoo_leads
LEFT JOIN votes
ON lists.id = votes.listid
LEFT JOIN comments
ON lists.id = comments.listid
GROUP BY lists.id
ORDER BY COUNT(votes.id) DESC,
COUNT(comments.id) DESC
LIMIT 10
That is because you have ORDER BY Comments.listid
in your SQL statement.
精彩评论