MySQL ranking one user based on cell value

Question

I got to figure out how to optimally check for a users rank in all the stages he has passed in a game. The rank is based on the amount of time (float) it took for the player to pass the stage.

What I've got is this schema: http://www.sqlfiddle.com/#!9/3764e/1

id| fTime | stage |
-------------------
1 | 11.123| first |
1 | 12.561| second|
1 | 13.333| third |
2 | 11.642| first |
2 | 12.231| second|
2 | 13.999| third |
3 | 11.000| first |
3 | 12.842| second|
3 | 13.849| third |
4 | 11.535| first |
4 | 12.653| second|
4 | 13.145| third |
5 | 11.653| first |
5 | 12.134| second|
5 | 13.678| third |
6 | 11.546| first |
6 | 12.325| second|
6 | 13.532| third |

The result that I need is the players rank in each stage he has passed, based on his time in it (fTime).

|     id|     stage| rank|
|      1|   'first'|    1|
|      1|  'second'|    2|
|      1|   'third'|    1|

The way I've been doing this thus far, is by querying the stages the player has finished, then the time he has achieved in each stage, and then getting his rank on that stage. This is obviously not optimal, as I got to do a lot of trips to the mysql server and back, when it could be done in way less queries.

I've been looking around the net for issues like this, but haven't found anything that quite matches, and this goes beyond my SQL skills..

Answer 1

There are two basic ways to do it:

A join (usually preferred in MySQL)

SELECT a.id, a.stage, count(b.id)+1 AS rank
FROM playertimes a 
LEFT JOIN playertimes b on (a.stage=b.stage and a.fTime > b.fTime)
group by a.id, a.stage;

And a dependent subquery which may be a lot slower in some cases but might work OK for you

SELECT id, stage, 
  (
    select count(1)+1
    from playertimes b
    where a.stage=b.stage and a.fTime > b.fTime
  ) AS rank
FROM playertimes a
order by id, stage;

You can check it at http://www.sqlfiddle.com/#!9/2d9580/3 - I added an index on (stage, fTime) which can make it faster in both cases.

Both solutions will assign the same rank to players with the same time (if it can happen at all) and then skip the "unused" ranks - like 1, 2, 2, 2, 5, 6, 6, 8.

Answer 2

Try this query. Is it what you looking for?

    select p.id,p.stage,r.rank from playertimes p  
    inner join  
     (select id,ftime,@rank:=if(@prev=concat_ws('',stage),@rank+1,1)as rank,  
      @prev:=concat_ws('',stage) as stage from playertimes order by stage asc,  
      ftime asc) as r  
      on p.id=r.id and p.stage=r.stage order by 1,2