Mysql Ranking Query on 2 columns

Question

Table

  id       user_id     rank_solo     lp
   1             1            15     45
   2             2             7     79
   3             3             17    15

How can I sort out a ranking query that sorts on rank_solo ( This ranges from 0 to 28) and if rank_solo = rank_solo , uses lp ( 0-100) to further determine ranking? (If lp = lp, add a ranking for no tie rankings)

The query should give me the ranking from a certain random user_id. How is this performance wise on 5m+ rows?

So

User_id 1 would have ranking 2

User_id 2 would have ranking 3

User_id 3 would have ranking 1

Possible duplicate of [MySQL - Get row number on select](http://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select) — Sam Segers, Jan 16 '16 at 02:45

score 2 · Answer 1 · answered Jan 16 '16 at 02:57

2

You can get the ranking using variablesL

select t.*, (@rn := @rn + 1) as ranking
from t cross join
       (select @rn := 0) params
order by rank_solo desc, lp;

answered Jan 16 '16 at 02:57

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Jan 16 '16 at 02:26

0

You can use ORDER BY to sort your query:

SELECT *
FROM `Table`
ORDER BY rank_solo, lp

answered Jan 16 '16 at 02:26

wogsland

9,106
19
57
93

score 0 · Answer 3 · answered Jan 16 '16 at 02:32

I'm not sure I quite understand what you're saying. With that many rows, create a query on the fields you're using to do your selects. For example, in MySQL client use:

create index RANKINGS on mytablename(rank_solo,lp,user_id);

Depending on what you use in your query to select the data, you may change the index or add another index with a different field combination. This has improved performance on my tables by a factor of 10 or more.

As for the query, if you're selecting a specific user then could you not just use:

select rank_solo from table where user_id={user id}

If you want the highest ranking individual, you could:

select * from yourtable order by rank_solo,lp limit 1

Remove the limit 1 to list them all.

If I've misunderstood, please comment.

With ranking I mean whats the ranking of the user in relation to all the other users in the table ( Wasnt clear, my bad ) — battlenub, Jan 16 '16 at 02:39

score 0 · Answer 4 · answered Jan 16 '16 at 05:28

An alternative would be to use a 2nd table.

table2 would have the following fields:

rank  (auto_increment)
user_id
rank_solo
lp

With the rank field as auto increment, as it's populated, it will automatically populate with values beginning with "1".

Once the 2nd table is ready, just do this when you want to update the rankings:

delete from table2;

insert into table2 select user_id,rank_solo,lp from table1 order by rank_solo,lp;

It may not be "elegant" but it gets the job done. Plus, if you create an index on both tables, this query would be very quick since the fields are numeric.

Mysql Ranking Query on 2 columns

4 Answers4