Question for SQL gurus: how can I select distinct and sort by number of duplicates?

I have a table with 3 fields: zip, city, state. One row for every zip code in the US, it contains about 39,000 rows.



I want to search by city name alone. Some cities, such as "Rockford", will return many rows, in several different states.



If I do "SELECT * FROM ziplist WHERE city = 'Rockford'", I might get:



zip - city - state

=================

00123 Rockford MI

00124 Rockford MI

00125 Rockford MI

60109 Rockford IL

60110 Rockford IL

60111 Rockford IL

60112 Rockford IL

91832 Rockford CA



3 Rockfords in MI, 4 Rockfords in IL, 1 Rockford in CA.

I need a query that will return a table that looks like this:



city - state

=================

Rockford IL

Rockford MI

Rockford CA



So that a user can choose which state he meant to search for, with the most probable result at the top. Very simple; distinct state, sorted by the number of times that state was repeated.



I'm using MySQL Server 4.1.13a.Question for SQL gurus: how can I select distinct and sort by number of duplicates?
I'm not sure I count as a SQL guru, but I have something you could try.



SELECT state, count(city) as cityCount

FROM ziplist

WHERE city='Rockford'

GROUP BY state -- Of course!

ORDER BY cityCount DESC



Good luck!
if you just need the City and State without the zip codes you should do this



select distinc city,state from ziplist order by state,city



Rockford CA

Rockford IL

Rockford MI



but if you select the zip code, you will get all the zip codes.Question for SQL gurus: how can I select distinct and sort by number of duplicates?
Try:



SELECT Distinct* FROM zipList WHERE city = 'Rockford'Question for SQL gurus: how can I select distinct and sort by number of duplicates?
Beware: you are making the assumption that the number of zip codes for a particular city also tracks the likelihood of the city being the one the user wanted, and then using that to order the cities from most- to least-likely. That may not be true, and runs counter to most people's expecation that cities will be listed alphabetically. For example there are something like 55 "Springfield" cities/towns in the US. I would think that it would be best to show the cities in alphabetic order by state abbreviation, than by number of zip codes.



[Yeah, I know this isn't an answer per se, but gman already provided the right answer according to your requirements. I am just trying to point out potential semantic/logical errors in your requirements.]