0

OK, I tried asking this and it was marked as duplicate for some other question that DOES NOT ANSWER what I'm trying to ask. So if you guys could actually read the question before dismissing it, that would be great.

I have a table where I register user's performance on some games(lets call it 'scores'. The table is basically: id, id_user, id_game, game_level, score and time.

Then I have a table (lets call it 'games') where I store the game info, basically: id, name, levels, description and so on.

So, what I need now is to get the best results (score desc, time asc) for each GAME AND LEVEL pairing for a determined user, while retrieving the game info from games table.

What I tried was:

SELECT id_game
     , game_level
     , score
     , time 
  FROM scores 
 where id_user = 4 
 order 
    by id_game asc 
     , game_level asc
     , score desc
     , time asc

And I get all the results ordered exactly how I want. Then I use it as a derived table to order the results, like this:

SELECT * 
  from 
     ( SELECT id_game
            , game_level
            , score
            , time 
         FROM scores 
        where id_user = 4 
        order 
           by id_game asc
            , game_level asc
            , score desc
            , time asc
       ) A 
   group 
      by id_game
       , game_level

And it does group, but it doesn't show the first result of each set (highest score AND lowest time). I thought maybe it would show the last result on groups, so I tried reversing the score logic, but didn't work as well.

What am I doing wrong, and is there a simpler way to do it?

Ps.: I didn't even try to join the games table to get game info since I wasn't able to get the results I wanted.

OK, here's sample data:

+----+---------+------------+-------+-------+---------+
| id | id_game | game_level | score | time  | id_user |
+----+---------+------------+-------+-------+---------+
|  1 |       1 |          1 |    70 | 01:20 |       4 |
|  2 |       1 |          1 |    70 | 01:17 |       4 |
|  3 |       1 |          2 |    66 | 00:44 |       4 |
|  4 |       1 |          2 |    64 | 00:22 |       4 |
|  5 |       1 |          3 |   100 | 03:24 |       4 |
|  6 |       1 |          4 |    99 | 01:29 |       4 |
|  7 |       1 |          4 |    99 | 01:23 |       4 |
+----+---------+------------+-------+-------+---------+

And here is the expected result:

+---------+------------+-------+-------+
| id_game | game_level | score | time  |
+---------+------------+-------+-------+
|       1 |          1 |    70 | 01:17 |
|       1 |          2 |    66 | 00:44 |
|       1 |          3 |   100 | 03:24 |
|       1 |          4 |    99 | 01:23 |
+---------+------------+-------+-------+

I found many answers explaining how to do this for one order by argument, but none for two.

user3317315
  • 1
  • 2
  • It really is a duplicate. https://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html – Strawberry May 15 '18 at 16:22
  • @Strawberry It's not a duplicate because what I need is not only the group wise maximum. It's the maximum for a column and at the same time the minimum for a different column, and then group the results by two other columns. There's no answer here covering this. – user3317315 May 15 '18 at 16:32
  • Look - it's exactly the same, just more subqueries... http://sqlfiddle.com/#!9/7684e4/1 - if you upgrade to MySQL 8.0, you can use windowing functions/common table expressions, which simplifies this whole process. – Strawberry May 15 '18 at 16:46
  • @Strawberry That's the problem I was having, I didn't know how to organize the subqueries. You should write this as an answer because it's not obvious for the non-specialist what you did. Thanks for the help. – user3317315 May 15 '18 at 17:49
  • I would, but honestly this question, and every variation on it, has been answered hundreds of times – Strawberry May 15 '18 at 18:12

0 Answers0