MySQL show ranks of multiple columns

3
votes

I have the table that looks like this:

Name Height Weight
Jim  60     150
Tom  62     170
Mac  64     160

I would like to find a query that returns something like this:

Name Height Weight Height_Rank Weight_Rank
Jim  60     150    3           3
Tom  62     170    2           1
Mac  64     160    1           2

What is the best way to show the ranks of multiple columns as I described above? I could use order by to find the rank of a single column but I would like to have the rank of multiple columns in a single table. Thanks!

EDIT: The answer below is a good solution. However, if you are ranking thousands of rows you will likely run into a another problem. "group_concat" has a maximum length of 1024 bytes. You can increase this limit by running "SET SESSION group_concat_max_len = 1000000;". This will allow you to rank many more rows at once.

1
mysqldatabase
It seems you will need a nested query per each additional column, as shown here. – PM 77-1
Thanks! That is what I am looking for but I was hoping there is a better way. – user985637
Few mins I will Provide you how you can – Choco

1 Answers

1
votes

Use Some thing Like this..

SELECT Name,Height,Weight,FIND_IN_SET( Height,( SELECT GROUP_CONCAT( Height ORDER BY Height DESC ) FROM scores )) AS Height_Rank,FIND_IN_SET( Weight,( SELECT GROUP_CONCAT( Weight ORDER BY Weight DESC ) FROM scores ) ) AS Weight_Rank FROM scores