SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Finding the Most Popular Field Values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/08/2000 :  11:30:18  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Kobiashi Maru writes "Say you have a table with all the zipcodes in the United States. The fields are Zipcode, City, State Abreviation. It's pretty easy to find the Cities and States with the most zipcodes. But how would you build a query that would find the City Name that is the most "popular". For example, New York City may have the most zip codes, but not very many states have a NYC. Lots of states have a "Springfield". So which city name is most popular among the US?" Ok, I'll make you a deal. I'll provide the query but you have to report back with the answer.

Article Link.

Anonymous
Starting Member

0 Posts

Posted - 09/10/2000 :  10:43:53  Show Profile  Reply with Quote
Here's the Results I got....

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

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 09/21/2000 :  13:14:24  Show Profile  Reply with Quote
Most Popular Cities

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?

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 09/22/2000 :  15:13:09  Show Profile  Reply with Quote
Can't be done without more information...

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

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 10/05/2000 :  08:41:07  Show Profile  Reply with Quote
Getting closer

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?

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 10/26/2000 :  08:17:51  Show Profile  Reply with Quote
Unbelieveable...

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...

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000