CODEWITHSUNDEEP
mysqldatabase
user985637
user985637

Reputation: 115

MySQL show ranks of multiple columns

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.

Upvotes: 3

Views: 617

Answers (1)

Choco
Choco

Reputation: 1064

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

Upvotes: 1

Related Questions