How do I view all records with data source as 1 if not 1 then use data source 2 but no duplicates-CodePudding

I have a table that has the below columns

sk_item_id  item_code       item_desc                    sk_data_source
1           054486900       TRIM lh hotplate Whi                1
2           054486900       TRIM lh hotplate Whi                2
3           081423698       ARC3084 DG FRIT                     2
4           081744900       HANDBOOK<600G MK2>44PGX500          1

I want to display the records that are contained within data source 2 but if the record does not sit in data source 2 then display the record that is in data source 1 but I do not want any duplicates

so basically display all data from data source 2 if the item code is not in data source 2 then display the record

CodePudding user response：

You can use row_number() with a case expression to prioritise rows with sk_data_source = 2:

with cte as (
  select *, 
    row_number() over(partition by item_code, item_desc 
      order by case when sk_data_source = 2 then 1 else 2 end) rn 
  from 
    table_name
) 
select 
  sk_item_id, 
  item_code, 
  item_desc, 
  sk_data_source 
from cte 
where rn = 1;

Fiddle

CodePudding user response：

If using Postgresql, do DISTINCT ON:

select distinct on (item_code) sk_item_id, item_code, item_desc, sk_data_source 
from table_name
order by item_code, sk_data_source desc;