SQL Question, MAX records that max column

[TechBoyJK]

Hi Guys,

I'm querying a zip code database to find where records match a city name.

For instance, if I query the database for 'Saint Louis' I will get a bunch of matches for St. Louis, MO (one for every zip code), and a few matches for smaller towns in other states, that just happen to also be named Saint Louis.

SELECT city, state, zipcode

FROM zipcodes

WHERE city = 'saint louis'

I need to find which set of results has the most zip code matches for a particular state.

So with the above query, I'd get about 71 records returned, with all but 3 being from the Saint Louis in Missouri.

How could I figure out which CityName has the zip code most matches for a state? I'm trying to figure which city has the most zips.

 

[dfnkt]

SELECT COUNT(zipcode) AS ZipCode, state
FROM zipcodes
WHERE city = 'saint louis'

I'll do some checking, but try that

EDIT:
--Working code
SELECT COUNT(zipcode) AS ZipCode, state
FROM zipcode
WHERE city = 'saint louis'
Group by state

 

[TechBoyJK]

SELECT COUNT(zipcode) AS ZipCode, state
FROM zipcodes
WHERE city = 'saint louis'

I'll do some checking, but try that

EDIT:
--Working code
SELECT COUNT(zipcode) AS ZipCode, state
FROM zipcode
WHERE city = 'saint louis'
Group by state

Ok, so that counts em.. What's the best way to grab which state has the most records? Just the one at top?? Sort asc, then maxrow=1?


ps! thanks! im at work so I can't fiddle/experiment with the code right now.. so i try and figure out what I need to do before I have a chance so I can tear through more work at home.

 

[dfnkt]

SELECT COUNT(zipcode) AS ZipCode, state
FROM zipcode
WHERE city = 'saint louis'
Group by state
Order By zipcode DESC

At first I thought that the state with the most would automatically be on top because in my test database I defined Missouri with 7 zip codes for Saint Louis and then Delaware with 8. Delaware came up on top, I then realized it was alphabetized because I added 9 entries for Wyoming (Wa) and it was listed on the bottom.

This was quickly fixed by adding Order by zipcode DESC

 

[TechBoyJK]

This is what I had figured out but i'm getting a syntax error on line 2 at count(zipcode)

select top 1 state, zipcode

count(zipcode) as zips

from zipcodes

where city = '#city_match#'

group by state

order by zips desc

 

[dfnkt]

Try ordering by zipcode instead of "Zips", perhaps your line 2 syntax error is an issue because of that line.

 

[Chapbass]

Looks like you're missing a comma after zipcode on line 2.


EDIT: Sorry, line one, at the very end of the line. if you were to take out the whitespace, it would be

select top 1 state, zipcode count(zipcode) as zips

but you need

select top 1 state, zipcode, count(zipcode) as zips

 

[Evadman]

SELECT TOP 1 state FROM zipcodes WHERE city = 'saint louis' GROUP BY state ORDER BY Count(*) DESC

 

[clamum]

SELECT TOP 1 state FROM zipcodes WHERE city = 'saint louis' GROUP BY state ORDER BY Count(*) DESC

I was going to post a slightly different query with a sub-query but I think this works and is more efficient.

 

[dfnkt]

SELECT TOP 1 state FROM zipcodes WHERE city = 'saint louis' GROUP BY state ORDER BY Count(*) DESC

Agreed, best way to go about it.

 

[TechBoyJK]

SELECT TOP 1 state FROM zipcodes WHERE city = 'saint louis' GROUP BY state ORDER BY Count(*) DESC

What's the advantage to TOP 1 in the SQL vs leaving it out but declaring "maxrows=1" in the cfquery tag (im using cfml)

 

[Evadman]

maxrows=1 only works in your tool. The query I posted will work in any MS SQL system in case you upgrade. In addition, the maxrows=1 will be hidden away from where expected in case anyone else needs to figure out what you were doing.