SELECT DISTINCT not DISTINCT!

Forum Moderators: open

Message Too Old, No Replies

SELECT DISTINCT not DISTINCT!

DISTINCT across 2 or more fields

jerreye

4:58 am on Aug 2, 2007 (gmt 0)

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

5:07 am on Aug 2, 2007 (gmt 0)

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

1:52 am on Aug 8, 2007 (gmt 0)

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

Duluth, Georgia
Duluth, Minnesota

Welcome to WebmasterWorld, jerreye.

syber

12:22 pm on Aug 8, 2007 (gmt 0)

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

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.