I have two tables:
post:
id text
1 abc
2 abcd
3 bcd
and voting:
post_id vote
2 2
1 5
3 1
1 3
I want to show top 10 posts by rating in "votes" table:
SELECT * FROM post
WHERE id IN
(SELECT post_id FROM
(SELECT SUM(vote) as totalvote, post_id
FROM voting
GROUP BY post_id) as table1
ORDER BY totalvote DESC)
LIMIT 10"
but it's order by post id. How can i order it by total votes?
Probably this is what you need:
SELECT post.id, post.text FROM post
JOIN voting ON voting.post_id = post.id
GROUP BY post.id
ORDER BY AVG(vote) DESC
LIMIT 10
At least I think it would make sense to order by average rating. But of course you can order by total points given:
SELECT post.id, post.text FROM post
JOIN voting ON voting.post_id = post.id
GROUP BY post.id
ORDER BY SUM(vote) DESC
LIMIT 10
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments