Get user's highest score from a table

Question

I have a feeling this is a very simple question but maybe i'm having brain fart right now and just can't seem to figure out how to go about it.

I have a MySQL table structure like below

+---------------------------------------------------+
| id |     date     |  score  |  speed  |  user_id  |
+---------------------------------------------------+
|  1 |  2016-11-17  |  2      | 133291  |     17    |
|  2 |  2016-11-17  |  6      | 82247   |     17    |
|  3 |  2016-11-17  |  6      | 21852   |     17    |
|  4 |  2016-11-17  |  1      | 109338  |     17    |
|  5 |  2016-11-17  |  7      | 64762   |     61    |
|  6 |  2016-11-17  |  8      | 49434   |     61    |

Now i can get a particular user's best performance by doing this

SELECT * 
FROM performance 
WHERE user_id = 17 AND date = '2016-11-17'
ORDER BY score desc,speed asc LIMIT 1

This should return the row with ID = 3. Now what I want is a single query to run to be able to return that 1 such row for each unique user_id in the table. So the resulting result would be something like this

+---------------------------------------------------+
| id |     date     |  score  |  speed  |  user_id  |
+---------------------------------------------------+
|  3 |  2016-11-17  |  6      | 21852   |     17    |
|  6 |  2016-11-17  |  8      | 49434   |     61    |

Also further more, can I have another question within this same query that would further sort this eventual resulting table by the same criteria of sort (score desc, speed asc). Thanks

Answer 1

Is easy using variable to emulate row_number() over (partition by Order by)

Explanation:

1. First create two variables in the subquery.
2. Order by user_id so when user change the @rn reset to 1
3. Order by score desc, speed asc so each row will have a row_number, and the one you want always will have rn = 1
4. @rn := you change @rn for each row
   • if you have a new user_id then @rn is set to 1
   • otherwise @rn is set to @rn+1

SQL Fiddle Demo

SELECT `id`, `date`, `score`, `speed`, `user_id`
FROM (
        SELECT *,
               @rn := if(@user_id = `user_id`, 
                         @rn + 1 , 
                         if(@user_id := `user_id`,1,1)
                        ) as rn           
        FROM Table1
        CROSS JOIN (SELECT @user_id := 0, @rn := 0) as param
        WHERE date = '2016-11-17' 
        ORDER BY `user_id`, `score` desc, `speed` asc
     ) T
where T.rn =1      

OUTPUT

Answer 2

A simple method uses a correlated subquery:

select p.* 
from performance p
where p.date = '2016-11-17' and
      p.id = (select p2.id
              from performance p2
              where p2.user_id = p.user_id and p2.date = p.date
              order by score desc, speed asc
              limit 1
             );

This should be able to take advantage of an index on performance(date, user_id, score, speed).

Answer 3

For mysql

You can try with a double in subselect and group by

  select * from    performance
  where (user_id, score,speed ) in (

            SELECT user_id, max_score, max(speed)
            FROM performance 
            WHERE (user_id, score) in (select user_id, max(score) max_score
                                       from performance 
                                       group by user_id)
            group by user_id, max_score
  );