mysql order by count

Question

I have a table with store IDs and the city where the store is located.

I want to list all the stores starting with the stores that are in the city where there is the most stores.

TABLE

ID    CITY
1     NYC
2     BOS
3     BOS
4     NYC
5     NYC

The output I want is the following since I have the most stores in NYC, I want all the NYC location to be listed first.

1 NYC
4 NYC
5 NYC
2 BOS
3 BOS

I can't seem to figure it out. Thanks.

Answer 1

SELECT count(City), City
FROM table
GROUP BY City
ORDER BY count(City);

OR

SELECT count(City) as count, City
FROM table
GROUP BY City
ORDER BY count;

Ahh, sorry, I was misinterpreting your question. I believe Peter Langs answer was the correct one.

Answer 2

This one calculates the count in a separate query, joins it and orders by that count:

SELECT id, city
FROM cities c
JOIN ( SELECT city, COUNT(*) cnt
       FROM cities
       GROUP BY city
     ) c2 ON ( c2.city = c.city )
ORDER BY c2.cnt DESC;

Answer 3

This solution is not a very optimal one so if your table is very large it will take some time to execute but it does what you are asking.

 select c.city, c.id, 
      (select count(*) as cnt from city c2 
       where c2.city = c.city) as order_col
 from city c
 order by order_col desc

That is, for each city that you come across you are counting the number of times that that city occurs in the database.

Disclaimer: This gives what you are asking for but I would not recommend it for production environments where the number of rows will grow too large.