While working on generating rankings for a game, I wrote a query similar to the following code (a lot of columns left out for brevity), and noticed the rank was producing seeming gibberish (first query). However changing the table in which gamenumber was referenced from produces the correct result (second query).
mysql> select @i:=@i+1 as rank, a.usernum, s.buildings from account_data as a, statistics as s join (select @i:=0) rank
where a.gamenumber=144 and a.usernum=s.usernum order by s.buildings desc limit 10;
+------+---------+-----------+
| rank | usernum | buildings |
+------+---------+-----------+
| 49 | 31071 | 87557 |
| 45 | 31047 | 86858 |
| 24 | 31064 | 84753 |
| 69 | 31089 | 79682 |
| 17 | 31103 | 76892 |
| 38 | 31106 | 66186 |
| 29 | 31053 | 65069 |
| 47 | 31081 | 59093 |
| 61 | 31036 | 58056 |
| 100 | 31061 | 56833 |
+------+---------+-----------+
10 rows in set (0.01 sec)
mysql> select @i:=@i+1 as rank, a.usernum, s.buildings from account_data as a, statistics as s join (select @i:=0) rank
where s.gamenumber=144 and a.usernum=s.usernum order by s.buildings desc limit 10;
+------+---------+-----------+
| rank | usernum | buildings |
+------+---------+-----------+
| 1 | 31071 | 87557 |
| 2 | 31047 | 86858 |
| 3 | 31064 | 84753 |
| 4 | 31089 | 79682 |
| 5 | 31103 | 76892 |
| 6 | 31106 | 66186 |
| 7 | 31053 | 65069 |
| 8 | 31081 | 59093 |
| 9 | 31036 | 58056 |
| 10 | 31061 | 56833 |
+------+---------+-----------+
10 rows in set (0.00 sec)
Can anyone explain to me why the different results in rank when the order is correct?