MySQL / distinct / order by question

[isorg]

I have a MySQL table, where there are x number of records. Let's say each record contains a "city" field, which may be New York, London, Paris, Madrid etc., and in other fields there is other data.

I want to construct a query which gives a list of distinct cities in the table: "SELECT DISTINCT city FROM table ORDER BY city ASC" which gives the result "London, Madrid, New York, Paris". So far so good.

However, I want to LIMIT the list to the 3 most common cities in the table...

Could someone please tell me how to LIMIT the list in this way?

[tstaheli]

You could use the MySQL count function and then make an alias to reference the count. Something like this.

SELECT count(City) as CityCount, City FROM `citydb` GROUP by City ORDER by CityCount DESC LIMIT 3

Let me know if that works

[isorg]

Wow! Works perfectly, just what I wanted. Thanks a million!