0

I'm having some troubles under MYSQL and hope find some answer here.

I have some score table, and want to query the users whose rank is nearby the current user. For instance, if the current user is ranked #85, I want to get the users whose rank is 80 to 90.

This is an extract of my score table

uuID  |  game   |  score  |attempt  |lastAttempt  
id3   |Gravity  |  1186   |156      |2016-06-30 23:58:09 
id2   |Gravity  |  1376   |2        |2015-07-18 22:45:23 
id1   |Gravity  |  1759   |165      |2016-06-17 18:13:28
id1   |Primality|   242   |1        |2016-04-28 23:20:32 
id4   |Primality|    35   |2        |2016-05-02 21:37:07

of course, the score table does not holdthe rank, it is dynamically calculated when I query the database. And I have a user table that gives me its name (and some other information, irrelevant for my issue) :

uuID | userName
id1  | Mathew
id2  | Jon

I made this query to order the scores and attribute each user a rank, for example for the game "Gravity" :

SELECT @rank:=@rank+1 rank,             
       IF(@score=s.score, @rank2:=@rank2, @rank2:=@rank) rank2, 
       s.uuID, 
       Users.userName, 
       @score:=s.score score, 
       s.attempt 
FROM 
       Scores s, 
       (SELECT @rank:=0, @score:=0, @rank2:=1) r, 
       Users 
WHERE   
       Users.uuID=s.uuID
       AND s.game='Gravity'
ORDER BY score DESC, attempt ASC

This is a standard technique found on several forums : I join to the scores a temp table with a user-defined variable that increments... and just join the user table to get the friendly name. Note that I actually use 2 "rank" variables, because I want the same scores to have the same rank (and same score with a different number of attempts have different rank) Result is OK :

rank |rank2  |uuID  |userName  |score |attempt   
1    |1      |id3   |Simon     |4910  |368 
2    |2      |id5   |youssef   |4284  |246 
3    |3      |id2   |Anonymous |2688  |97
4    |4      |id7   |Anonymous |2530  |12 
5    |5      |id8   |Anogjh    |2295  |24 
6    |5      |id9   |Nick      |2295  | 7

However, if I use this query as a subquery, the result is completely random :

SELECT * FROM (XXX) temp

(XXX being the previous select with rank and rank2) gives me :

rank  |rank2  |uuID  |userName  |score | attempt 
19    |19     |  id3 |Simon     |4910  |368 
28    |28     |  id5 |youssef   |4284  |246 
26    |26     |  id2 |Anonymous |2688  |97 
32    |32     |  id7 |Anonymous |2530  |12

so the result is still displayed ordered by score and attempt, but the rank does not give the good result anymore. It seems that now, the ascending rank follows an ordering of the uuID ascending

Remember that I do this because my aim is to query players whose rank is from 80 to 90 :

SELECT * FROM (XXX) temp WHERE rank BETWEEN 80 AND 90

Can someone explain me what happens with the subquery ? Maybe someone has an idea to get my result with another technique ?

EDIT : I corrected the userName column in description, I'll prepare a sqlfiddle

freelance_dev
  • 1
  • 2
  • data sample provided `display_name` in query and result sets is `username`. real result http://sqlfiddle.com/#!9/20b9d7/2 does not match to the `Result is OK:` – Alex Jul 06 '16 at 21:04
  • please prepare proper sqlfiddle – Alex Jul 06 '16 at 21:06
  • Well... I prepared the sqlfiddle : http://sqlfiddle.com/#!9/ae84d/1/0 but I don't get the same result as on my server, it seems the rank is joined to the table before being ordered by score (meaning I get the rank associated to order of insertion in database). What I need is to order by score first, then a join with the rank, I'll explore this possibility. So even with the inner query I have a difference. my MySQL in in version 5.5.46, if this helps. – freelance_dev Jul 07 '16 at 11:30
  • I see no issue with your query: http://sqlfiddle.com/#!9/ae84d/4 – Alex Jul 07 '16 at 12:09

0 Answers0