Order by COUNT per value

Question

I have a table which stores 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 are 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

user_232361 · Answer

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.

user_17343 · Answer

This one calculates the count in a separate query, joins it and orders by that count (SQL-Fiddle):

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

Vincent_Ramdhanie_27439 · Answer

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.

Order by COUNT per value

4 Answers