SELECT DISTINCT not DISTINCT!

DISTINCT across 2 or more fields

[jerreye]

Hi, I am trying to select only DISTINCT records of "City", "StateFullName" and "Population" field names from my ZIPCODES table.

-----------------------------------------------
SELECT DISTINCT City, StateFullName, Population
FROM _ZipCodeDatabase_US
WHERE Population > 20000
GROUP BY City, StateFullName, Population
-----------------------------------------------

This however, gives me duplicate Cities! I'm assuming this is because some cities have zipcodes with the same Population number attached to the row.

How do I ONLY select DISTINCT cities, even if a city has zipcodes with the same population number? I only want to return ONE of each City.

Any ideas?
Jeremy

[jerreye]

Holy smokes! A SECOND after posting this message I got the glorious revelation. I took out the Population field away from the DISTINCT clause and it worked perfect! DUH!

[coopster]

Unless you have a city of the same name in two different states ;)

Duluth, Georgia
Duluth, Minnesota

Welcome to WebmasterWorld, jerreye.

[syber]

You do not need to do both a SELECT DISTINCT and GROUP BY, either one should give you the same result.

SELECT City, StateFullName
FROM _ZipCodeDatabase_US
WHERE Population > 20000
GROUP BY City, StateFullName

OR

SELECT DISTINCT City, StateFullName
FROM _ZipCodeDatabase_US
WHERE Population > 20000

Since you are not displaying an aggregate function in the column list, the SELECT DISTINCT is more appropriate.