Order by and Count

Hi

I have been trying to do a similar thing for a number of different
tables but have given up as i could not find a way to do it but it
seems an obvious thing many pople would want to do so i thought i would
ask once more to see if it is possible.

I have a mysql table like this
Id01 Maidstone KENT
Id02 Ashford KENT
Id03 Bristol AVON
Id04 Westminster LONDON
Id05 Bath AVON
Id06 Chatham KENT

Is there a way to order the table by the county and get total for the
number of rows fro meach county (for output to a coldfusion page) e.g.
KENT (3)
Chatham
Maidstone
Ashford
AVON (2)
Bristol
Bath
LONDON (1)
Westminster

Though formating does not matter

Regards

Richard

Re: Order by and Count

The easiest way is to do it in two steps. The first step is to make a
select that gives you all the different counties and the number they
appear. The second step is to overloop this first select and find all
the records that are belonging to the first.

This selects finds all the different counties and makes a count of it
ordered by quantity descending :

select distinct(county), count(*)
from cities
group by county
order by count(*) desc

Then you walk through the results of this select and you find the
records from KENT, ...