mysql: ORDER BY number of occurrences of an element in a column of mysql table

Question

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

It's rare that the thing one counts is also the thing one groups by! — Strawberry
– Strawberry, Commented Oct 3, 2014 at 21:47
The answer from @MindStalker is exactly what you need right now stackoverflow.com/questions/2283305/mysql-order-by-count — Logain
– Logain, Commented Oct 3, 2014 at 21:55

Strawberry · Accepted Answer · 2014-10-03 21:55:11Z

6

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;

answered Oct 3, 2014 at 21:55

Strawberry

34k14 gold badges43 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mickmackusa Over a year ago

This answer is missing its educational explanation.

Collectives™ on Stack Overflow

mysql: ORDER BY number of occurrences of an element in a column of mysql table

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related