mysql: ORDER BY number of occurrences of an elemen

问题:

Can someone please help me to 'ORDER' a mysql table according to the no. of occurrences of a string in one of the columns of same table.

I want to rearrange the table according to the number of times a location has occurred.

Please see the example below:

id    name       location
--    -----      --------
1     Mark       US
2     Mike       US
3     Paul       Australia
4     Pranshu    India
5     Pranav     India
6     John       Canada
7     Rishab     India

Expected result:

id    name       location
--    -----      --------
4     Pranshu    India
5     Pranav     India
7     Rishab     India
1     Mark       US
2     Mike       US
3     Paul       Australia
6     John       Canada

I tried this query:

SELECT *, COUNT(location) AS count FROM `tablename` GROUP BY `location` ORDER BY count DESC;

But it showed me the following result omitting the repeating occurrence of location:

id    name       location
--    -----      --------
4     Pranshu    India
1     Mark       US
3     Paul       Australia
6     John       Canada

回答1:

SELECT x.* 
  FROM my_table x 
  JOIN (SELECT location, COUNT(*) total FROM my_table GROUP BY location) y
    ON y.location = x.location
 ORDER 
    BY total DESC
     , id;