mysql ORDER results BY number of occurences in a column

Question

I have a database which will receive a whole bunch of information, but most importantly it will receive a city name for each row. Sample below:

id         city
1          London
2          Manchester
3          London
4          Brighton
5          Oxford
6          Oxford
7          London

I want to SELECT only the city name from this table, displaying the top 3 results in the order of which occurs most often.

So far I am using the query below which I found:

SELECT N.city, COUNT(*) AS howmany
FROM ( SELECT DISTINCT city FROM events ) AS N,
events AS T
WHERE N.city = T.city
GROUP BY N.city
ORDER BY howmany
LIMIT 0,2 

Answer 1

You can simplify your existing query to a single access to the events table, without needing to join it to itself:

SELECT N.city, 
       COUNT(N.city) AS howmany 
  FROM events AS N
 GROUP BY N.city 
 ORDER BY howmany 
 LIMIT 3

if you don't want to return the count, try:

SELECT N.city
  FROM events AS N
 GROUP BY N.city 
 ORDER BY COUNT(N.city)
 LIMIT 3

Answer 2

You don't need the additional join GROUP BY will already take care of the DISTINCT aspect for you.

SELECT city, COUNT(*) AS howmany
FROM 
events
GROUP BY city
ORDER BY howmany DESC
LIMIT 0,3 

Answer 3

If you only want to have the 'city' field in your final result:

SELECT city FROM (
  SELECT city, COUNT(*) as ccount
  FROM events
  GROUP BY city
)
ORDER BY ccount DESC
LIMIT 3

If you don't care about the fields in the final result, as long as city is one of them, it's even simpler:

SELECT city, COUNT(*) as ccount
FROM events
GROUP BY city
ORDER BY ccount DESC
LIMIT 3