1

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?

graham.reeds
  • 16,230
  • 17
  • 74
  • 137

1 Answers1

0

Using a variable in this way is something of a hack:

select @i:=@i+1 as rank

You could expect this to work if you select from only one table. When you join more tables together, MySQL might evaluate @i:=@i+1 for a row that is later filtered out by the where clause.

To solve this, ensure that the rank is calculated outside a join. One way is to store the join results in a temporary table, and calculate the rank on that.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Temporary tables aren't going to fly. There are 15 or so rankings and then for paying customers there are the actual placings for those users. – graham.reeds Jun 12 '12 at 09:27
  • Is there an alternative way of calculating the ranking? – graham.reeds Jun 12 '12 at 09:28
  • You can create thousands of temporary tables on the fly with no problem. In fact, MySQL uses temporary tables for many reasons, whether you ask for them or not. There are alternative ways but they are very expensive compared to the `@i:=@i+1` trick. – Andomar Jun 12 '12 at 09:39