ChrisG ChrisG - 7 months ago 24
SQL Question

If duplicates exist, select the value based on another column

I have a large list of zip codes and territories that I've combined from two different data sources.

My columns look like:
zipcode, territory, source

The values might look like:

76345, ShiPaTown, Source1
76345, ShiPaTown, Source2
12110, South Park, Source1
12110, Mars, Source2

My objective is to only have ONE row per unique zip code and if there's a record for a zip code in BOTH Source1 and Source2, to always take the territory from Source1.

So the previous list would get reduced to:

76345, ShiPaTown
12110, SouthPark


This is a prioritization query. Here is one approach:

select zip, town
from t
where source = 'source1'
union all
select zip, town
from t
where source = 'source2' and
      not exists (select 1 from t as t2 where = and t2.source = 'source1');