MySQL Leaderboard view

Question

I have a table with the following data

USER_ID | REVENUE
1  | 12,545
2  | 9,845
3  | 55,874

Is there a way for me to create a result with ranking on the fly within a view i.e.

USER_ID | REVENUE | RANK
3  |   55,874  |  1
1  |   12,545  |  2
2  |   9,845   |  3

I could try doing this by running a cron, but would like the result to be real-time - generated by MySQL in a view.

Any other alternatives welcome.

score 0 · Answer 1 · answered Nov 18 '13 at 20:50

0

It's a hack, but as two separate queries:

select @last:=0;

select USER_ID, REVENUE, @RANK := @RANK + 1 as RANK
from YOURTABLE
order by ...

answered Nov 18 '13 at 20:50

Marc B

problem doing it this way is that MySQL Views cannot use variables or parameters! – buzzmonkey Nov 18 '13 at 22:41

score 0 · Answer 2 · answered Nov 18 '13 at 21:26

0

Yes, it can be done in one query

select user_id, 
       revenue, 
       @rank := rank + 1 as rank
from your_table, (select @rank := 0) r
order by revenue desc

answered Nov 18 '13 at 21:26

juergen d

2 Answers2