Article Link.

Using the Census 1990 database of zipcodes, I got these results:

Houston = 101
Los Angeles = 56
Philadelphia = 52
Dallas = 51
Kansas City = 50

-Jason Gross
zoob@mn.mediaone.net
http://www.liquidwolf.com

But that doesnt cut it. You're correct, your answer is a straight forward solution....but it doesn't solve the problem. The result set is the city name with the most zipcodes, but that doesn't mean it is the city name used most often in the US!

To find the answer, you must first eliminate all the duplicate city, state combinations. If there are 150 zipcodes for cities called "Houston", you need to make sure narrow it down until you only have a subset that includes "Houston, MO" and "Houston, TX".

Once that is done, then you can run your query to count the city names.

Does that constitute stumped?

Kobiashi Maru - an unwinnable situation?

This gets you closer to a correct answer:

SELECT TOP 5 city, Number=count(*)
FROM (SELECT DISTINCT city, state FROM table GROUP BY city, state)
GROUP BY city
ORDER BY Number desc

This will only work if you assume that there are no duplicate town names within a given state. If there were two Springfield, Indiana's (there are), the above would only return it as one entry, thus giving you a lower number than really exists. There really isn't enough information to get a true answer to the problem.

Dave <><
siefker@mindless.com

But that is what you want to do. The quest was not to find the city name with the most zip codes, it was to find the city name most often used (repeated) in the US.

Actually, zip codes don't really have an influence in this problem.

Anyone have an answer?

Yea...for questions like those you really have to be a 'SQL Guru' to answer them. I will go back now, trying to challenge my intellect by watching Oprah...