Niiicou Niiicou - 7 months ago 20
SQL Question

MYSQL Multi group by and max

Here is a table called posts_votes

id|discussion_id|post_id|user_id|vote_sign|
__________________________________________
1 | 1 | 1 | 1 | 1 |
2 | 1 | 1 | 2 | -1 |
3 | 1 | 2 | 3 | 1 |
4 | 1 | 2 | 4 | 1 |
5 | 2 | 3 | 1 | -1 |
6 | 2 | 4 | 2 | 1 |


I want to create a view with theses results:

discussion_id|post_id|score
1 | 2 | 2
2 | 4 | 1


With :


  • post_id is the post with best score

  • score is SUM(vote_sign)



I'm torturing my mind with group by and having max but I find no way to do it.. =(

If somebody has an idea...

Thanks ;)

vkp vkp
Answer

Use sub-queries to first calculate the scores and select max score for each discussion_id. Then join the result sets to get the post with max score for each discussion_id.

select t1.*
from (select discussion_id,post_id,sum(vote_sign) as score
      from posts_votes
      group by discussion_id,post_id) t1
join (select discussion_id,max(score) as maxscore 
      from (select discussion_id,post_id,sum(vote_sign) as score
            from posts_votes
            group by discussion_id,post_id) t
      group by discussion_id) t2 
on t1.discussion_id = t2.discussion_id and t1.score = t2.maxscore