sql query unique/distinct row

I have a table of cities, states based on zip codes. So if someone searches chicago they'll get开发者_如何学编程 like 30 listings of chicago, il because of the number of zip codes. I'd like to only get one listing of chicago... but I'd also like to return the zip field too (because I don't need it exact). Below will return a single row of "chicago, il" but it won't also pass the zip... how can it also pass the zip.

SELECT DISTINCT City, State FROM Zips where City like 'chicago' ORDER BY State

Zips table

*Zip | City | State | Lat | Long

SELECT City, State, MIN(Zip) AS ZIP
  FROM Zips
 WHERE City = 'chicago'
 GROUP BY City, State
 ORDER BY State;

Or, maybe more informatively:

SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
  FROM Zips
 WHERE City = 'chicago'
 GROUP BY City, State
 ORDER BY State;

Would it also be possible to allow the input search to take a zip code and/or a city?

Search on zip code:

SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
  FROM Zips
 WHERE Zip = '01234'
 GROUP BY City, State
 ORDER BY State;

Search on zip and city:

SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
  FROM Zips
 WHERE City = 'chicago'
   AND Zip = '01234'
 GROUP BY City, State
 ORDER BY State;

Search on zip or city:

SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
  FROM Zips
 WHERE City = 'chicago'
    OR Zip = '01234'
 GROUP BY City, State
 ORDER BY State;

It is trickier to use a single query to search on zip (unless it is null) or city (unless that is null). It typically ends up using question mark placeholders etc - and is not wholly portable across DBMS (whereas the queries above are all portable to any SQL DBMS).

SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
  FROM Zips
 WHERE (? IS NOT NULL AND City = ?)
    OR (? IS NOT NULL AND Zip  = ?)
 GROUP BY City, State
 ORDER BY State;

Here, if it works, you'd provide the city parameter twice (once for each of the first two question marks) and the zip parameter twice (once for each of the last two question marks). This gets into the realm of discussing which programming language too. Some DBMS would allow you to write each parameter once, using a notation such as ':n' to identify the different parameters:

SELECT City, State, MIN(Zip) AS Min_ZIP, MAX(Zip) AS Max_ZIP
  FROM Zips
 WHERE (:1 IS NOT NULL AND City = :1)
    OR (:2 IS NOT NULL AND Zip  = :2)
 GROUP BY City, State
 ORDER BY State;

I don't know what MySQL provides in this area.

Assuming the Zip code is in a field called Zip, you could try:

SELECT City, State, MIN(Zip) 
FROM Zips 
WHERE City LIKE 'chicago'
GROUP BY City, State

For what you're describing, you're going to be forced to select either the MIN or MAX zip code.

If I understand that you don't care which zip code you get back I think you're looking for

LIMIT 1

Put it at the end of your SQL statement.

SELECT City, State, Zipcode FROM Zips where City like 'chicago' LIMIT 1;

Unless you've possibly got cities that occur in multiple states?

Assuming that Zips is unique on Zip, another solution would be:

Select Zip, City, State, Lat, Long
From Zips
Where Zip   =  (
                Select Min(Z1.Zip)
                From Zips As Z1
                Where Z1.City = 'chicago'
                )

That arbitrarily chooses the "lowest" zip code for any city named "chicago". However, I would be surprised that if that would suffice. A search for a zip code purely based on city name is never going to be logically sufficient. At the very least, you would want to narrow it by State or show all States which contain the requested city.

If you wanted to search for the zip code or city, you could do:

Select Zip, City, State, Lat, Long
From Zips
Where Zip   =   (
                    Select Min(Z1.Zip)
                    From Zips As Z1
                    Where Z1.City = 'chicago'
                        Or Z1.Zip = '12345'
                    )