« Where can I get sample follow-up letter for query letters sent to movie producers?
How good is a career as a Database Administrator? »

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.
gman: I think you meant "GROUP BY state",
otherwise it works great! Thanks!

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!

This entry was posted on Tuesday, December 29th, 2009 at 7:21 pm and is filed under select distinct. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “Question for SQL gurus: how can I select distinct and sort by number of duplicates?”

  1. 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!
    References :

  2. 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.
    References :
    tm_cgodinez@yahoo.com

  3. sinkablehail1978 on December 30th, 2009 at 1:03 am

    Try:

    SELECT Distinct* FROM zipList WHERE city = ‘Rockford’
    References :

  4. 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.]
    References :

Leave a Reply