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.
