Distinct Across Two Fields

Am I missing something?

[cabbagehead]

Perhaps my SQL skills are a bit rusty but I'm trying to do a query like this with no success:

select distinct(city,zip) from zip where state='CA'

...essentially, I want a list of distinct citys and zips. In otherwords, I only want to see Los Angeles appear once in the list, and I only want one zip code for Los Angeles. Same with San Francisco, etc.

Unfortunately in MySQL I'm being told I can only pass one field to the distinct function. Is there some other way of accomplishing my goal here?

Thanks.

[DrDoc]

No paretheses

SELECT DISTINCT city, zip FROM zip WHERE state = 'CA'

[cabbagehead]

Yep, I tried that. It doesn't work. It essentially ignores the distinct command in that case. :(

FYI - i'm using MySQL 4.1, in case its relevent.

sigh.

[syber]

That is standard basic ANSI SQL. Are you sure you don't have a typo?

Another possibilty would be to concatinate zipcode to city.

[cabbagehead]

> "That is standard basic ANSI SQL. Are you sure you don't have a typo? "

Like you said, its basic SQL - so wouldn't a type-o be pretty self-evident in the above examples?

I think there is something wrong with MySQL 4.1 perhaps? It appears that DISTINCT only worked if I'm only querying for 1 field.

This works:
SELECT DISTINCT location from zip where state='WA'

These do not work:
SELECT DISTINCT(location),zip from zip where state='WA'
SELECT DISTINCT location,zip from zip where state='WA'

... surely this not suppose to behave this way! Am I wrong? Anyone have experience trying to execute such a query on MySQL or version 4.1 in general? Is there a problem or some quirky mysql specific syntax I'm not aware or?

Thanks

[coopster]

What exactly is the issue? Are you not getting DISTINCT rows returned based on the criteria, or are you getting some form of error message?

[tsalmark]

distinct the more specific item maybe. Given I don't know your table structure etc, I can't really help, nor can anyone else.
Pass us the output from your command, presumable the error message and the output from desc zip and if it is small, the output from select * from zip where state='CA'.
I think maybe you want to ask:
select distinct(zip),city from zip where state='CA'
or
select distinct(city),zip from zip where state='CA'
if it helps you work it out you can join the table to itself on the key (lets call it ID)
select distinct(a.city),b.zip from zip as a, zip as b, where a.id = b.id and a.state = 'CA'

Or upgrade to a more ansi compliant database be that Mysql 5.1 or postrgres or Oracle 10.A or what ever.