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.
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;
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;