Clever Geek Handbook

MySQL shows rows of multiple columns

I have a 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 rows of multiple columns as I described above? I could use ordering to find the rank of a single column, but I would like to have the rank of multiple columns in the same table. Thank!

EDIT: The answer below is a good solution. However, if you are spanning thousands of lines, you are likely to run into a different 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 multiple rows at once.

+3


source to share


1 answer


Use something 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
+1


source






More articles:


All Articles