1

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

  • "Oops! Something went wrong. Try it again etc.", says SQL Fiddle to me. Now I don't have any idea about what your set-up looks like. Have you considered putting at least the most essential part of it (but preferably all) into your question. – Andriy M Dec 15 '15 at 9:28
2

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.

  • Thanks for the great solutions! I ended up with: 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; which took me around 10 seconds to complete, but after adding the indexes it took 0.09 seconds... So thanks a lot for that tip too! I guess 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; would have been the best solution, but I couldn't narrow it to one user. – J S Dec 15 '15 at 14:59
  • You can just replace group by a.id, a.stage with where a.id = X group by a.stage (or keep both columns in the group by anyway, it should not change the results.). – jkavalik Dec 15 '15 at 19:43
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.