mysql - Get max paired users scores with min time sums -

i have mysql tables this

users user_id | partner_id | name --------+------------+----- 1       | 2          | aaa 2       | 1          | bbb 3       | 4          | ccc 4       | 3          | ddd  games game_id | user_id --------+-------- 1       | 1 2       | 1 3       | 2 4       | 3 5       | 4 6       | 4  scores game_id | level | score | time --------+-------+-------+----- 1       | 1     | 1     | 10 1       | 2     | 1     | 10 1       | 3     | 1     | 10 2       | 1     | 0     | 20 2       | 2     | 0     | 20 2       | 3     | 0     | 20 3       | 1     | 1     | 30 3       | 2     | 1     | 30 3       | 3     | 1     | 30 4       | 1     | 1     | 2 4       | 2     | 1     | 2 4       | 3     | 1     | 2 5       | 1     | 1     | 5 5       | 2     | 1     | 5 5       | 3     | 1     | 5 6       | 1     | 1     | 3 6       | 2     | 1     | 3 6       | 3     | 0     | 3 

and need query sums points , time per game, looks this

game_id | user_id | sumpoints | sumtime --------+---------+-----------+-------- 1       | 1       | 3         | 30 2       | 1       | 0         | 60 3       | 2       | 3         | 90 4       | 3       | 3         | 6 5       | 4       | 3         | 15 6       | 4       | 2         | 9 

and need best scores per pair (where takes better score of 1 user), looks this:

user1_id | user2_id | sumpoints | sumtime ---------+----------+-----------+-------- 3        | 4        | 3         | 6 1        | 2        | 3         | 30 

that's final result. i'd appreciate if show me how should looks sql query. i'd mention first part solved jw 웃 in this post

thanks in advance.

something should work (this answers second query)

select    user_details.user_id,   user_details.partner_id,   score_details.score,   score_details.time      ( select          min(user_id) user_id,          max(user_id) partner_id                users        group         user_id + partner_id ) user_details     join      ( select          scores.game_id ,         games.user_id,         sum(score) score,          sum(time) time,         @row_num := if(@prev_value=games.user_id,@row_num+1,1) row_num,         @prev_value := games.user_id                 scores          inner join games on games.game_id = scores.game_id         inner join users on users.user_id = games.user_id       group         scores.game_id       order         user_id,          score     ) score_details on ( score_details.user_id = user_details.user_id , score_details.row_num = 1) 

the first part of join gets users along partners, users appearing first within pair displayed first, eg: if there 2 users id 1 , 2 consider user_id of user 1 appears first within pair.

the second query based on "echo_me" answer along row_number specifies ranking of scores each user, highest score has rank 1 every user.

sqlfiddle

hope helpful