-1

I'm working with the Riot Games API lately and got to the point where I need help or examples on how to achieve this. I have a database in mysql with the name "summoners" each summoner in the table looks like this:

enter image description here

There are 6 tiers (bad to good): BRONZE < SILVER < GOLD < PLATINUM < DIAMOND < CHALLENGER There are 5 divisions: I < II < III < IV < V

What I am trying to achieve is ranking them together (tier, division and leaguepoints) and output the rank number like this:

123311: RANK#1

234453422: RANK#2

123123: RANK#3

234234234: RANK#4

Not sure on how to do this, selecting a lot of data in the database will be hard on the server?

Alex K
  • 8,269
  • 9
  • 39
  • 57
Joel
  • 385
  • 1
  • 4
  • 13

1 Answers1

2
ORDER BY 
  FIND_IN_SET(tier,'CHALLENGER,DIAMOND,...'),
  FIND_IN_SET(division,'V,IV,III,II,I'),
  leaguepoints DESC;

However, it that's not performant enough for you, you could start using ENUM's and sort on those (mysql documentation)

If you need ranking directly in the query output (you might as well run a counter in application code though), look at this question

Community
  • 1
  • 1
Wrikken
  • 69,272
  • 8
  • 97
  • 136
  • Thanks, how would I retrieve only 1 of the results? my way of doing it would be just loop on what you have given above and getting the result index, but this wouldn't be a good idea if the table had over a 10,000 columns. Thanks again though! – Joel Nov 25 '14 at 21:42
  • s/columns/rows/ I presume. Well: frankly, I wouldn't calculate it on the fly in big sets, I would add a rank column, and I'd either make a cronjob run ever X time, or, if you fully control when the table gets updated and it's reasonably apart, run the ranking on update. You can combine the row-number answer with the sorting answer, and run an `UPDATE table SET rank=@row := @row + 1 ORDER BY ....` – Wrikken Nov 25 '14 at 21:45
  • I tried to update the way you showed me above but the rank field sets to 0 on all columns, my code is: `UPDATE stats SET rank = @row := @row + 1 ORDER BY FIND_IN_SET(tier,"CHALLENGER,DIAMOND,PLATINUM,GOLD,SILVER,BRONZE"), FIND_IN_SET(division,"V,IV,III,II,I"), leaguePoints DESC` should also point out that I did test it without the @row := @row + 1 and put 25 there and it worked fine. – Joel Nov 25 '14 at 22:28
  • never mind got it to work by setting the variable row to 0 `SET @row = 0`. Thanks for your help I will accept this answer! – Joel Nov 26 '14 at 00:33