CODEWITHSUNDEEP
mysql
battlenub
battlenub

Reputation: 187

Mysql Ranking Query on 2 columns

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

Upvotes: 0

Views: 98

Answers (4)

MichaelJ
MichaelJ

Reputation: 39

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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;

Upvotes: 2

MichaelJ
MichaelJ

Reputation: 39

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.

Upvotes: 0

wogsland
wogsland

Reputation: 9518

You can use ORDER BY to sort your query:

SELECT *
FROM `Table`
ORDER BY rank_solo, lp

Upvotes: 0

Related Questions