MYSQL Multi group by and max

Question

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 ;)

Answer 1

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

Answer 2

select SUBSTRING_INDEX(GROUP_CONCAT(post_id ORDER BY sm DESC), ',', 1)  AS top_post, discussion_id, max(score) as score
from (
    select discussion_id, post_id, sum(vote_sign) as score
    from posts_votes
    group by post_id, discussion_id
) c
group by discussion_id

Answer 3

select discussion_id, max(post_id), sum(vote_sign)
from posts_votes
group by discussion_id; 

Answer 4

Here the query corresponding to your question

select
  discussion_id, 
  max(post_id) as max_post_id,
  sum(vote_sign) as score
from
  posts_votes 
group by
  discussion_id;