CODEWITHSUNDEEP
sqldb2
Tony Borf
Tony Borf

Reputation: 4660

Distinct Values

I have a table with State, City, Town, and Zip code. The zip code has duplicate data since some zip codes have multiple towns. Now I need a query to return all 4 columns, however I do not want to repeat the zip code in the results. I only want to return 1 record per zip code. Where there are multiple towns I would like to return "-----------" in the result records to indicate multiple towns.

How can I do this?

Upvotes: 2

Views: 140

Answers (2)

bbfire
bbfire

Reputation: 3598

I'm assuming the zip codes can only be related to one city and state.

SELECT State
, City
, CASE WHEN MAX(Town) <> MIN(Town) THEN '-------' ELSE MAX(Town) END as Town
, ZipCode
FROM data
GROUP BY State, City, ZipCode

soomething like that? Not sure how efficient it is vs other options though.

EDIT: I agree that COUNT is a clearer way to express this

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147224

SELECT CASE WHEN COUNT(Town) > 1 THEN '-----' ELSE MIN(Town) END AS Town, Zip
FROM YourTable
GROUP BY Zip

Edit: if you also want to return other fields like the State, just add on e.g.:

SELECT 
    CASE WHEN COUNT(Town) > 1 THEN '-----' ELSE MIN(Town) END AS Town, 
    CASE WHEN COUNT(Town) > 1 THEN '-----' ELSE MIN(State) END AS State, 
    Zip
FROM YourTable
GROUP BY Zip

Upvotes: 2

Related Questions