MySQL - select rank for users in a score table

mysql, ranking, subquery

Solution

Although I'm not sure what "problematic" means in this context, here is the query rewritten as a plain `LEFT JOIN` with a subquery just to get the ranking right at the end (the `ORDER BY` needs to be done before the ranking);

SELECT user_id, score, @rank := @rank + 1 AS rank FROM
(
  SELECT u.user_id, u.score
  FROM user_score u
  LEFT JOIN user_score u2
    ON u.user_id=u2.user_id
   AND u.`timestamp` < u2.`timestamp`
  WHERE u2.`timestamp` IS NULL
  ORDER BY u.score DESC
) zz, (SELECT @rank := 0) z;

An SQLfiddle to test with.

EDIT: To take group_id into account, you'll need to extend the query somewhat;

SELECT user_id, score, @rank := @rank + 1 AS rank FROM
(
  SELECT u.user_id, u.score
  FROM user_score u
  LEFT JOIN user_score u2
    ON u.user_id=u2.user_id
   AND u.group_id = u2.group_id       -- u and u2 have the same group
   AND u.`timestamp` < u2.`timestamp`
  WHERE u2.`timestamp` IS NULL
    AND u.group_id = 1                -- ...and that group is group 1
  ORDER BY u.score DESC
) zz, (SELECT @rank := 0) z;

Another SQLfiddle.

Problem

I've got a 'user_score' table with that structure: ``` |id|user_id|group_id|score| timestamp | | 1| 1| 1| 500| 2013-02-24 18:00:00| | 2| 2| 1| 200| 2013-02-24 18:01:50| | 3| 1| 2| 100| 2013-02-24 18:06:00| | 4| 1| 1| 6000| 2013-02-24 18:07:30| ``` What I need to do is to select all users from that table which are from the exact group. Select their actual (according to timestamp) score in that group and their rank. What I have is (edit: after Jocachin's comment I found out that my own query does not work as I expected, sorry to all): ``` SELECT user_id, score, @curRank := @curRank + 1 AS rank FROM ( SELECT * FROM ( SELECT * FROM `user_score` WHERE `group_id` = 1 ORDER BY `timestamp` DESC ) AS sub2 GROUP BY `user_id` ) AS sub, (SELECT @curRank := 0) r ORDER BY `rank` ``` Expected result for example data and group_id = 1: ``` |user_id|score|rank| | 1| 6000| 1| | 2| 200| 2| ``` But MySQL subselects are a bit problematic, do you see any other solution, please? I'll probably need to get the rank od single user in the group later. I am lost at the moment.

Original source