I have data as below. Some items have more then one type id. I need to select only one from each id.

My condition is that

if exists (type id of item == 1) select only that row;

if not exists type id is 1 check if exists (type id of item == 2) select that row;

if not exists type id is 2 check if exists (type id of item == 3) select that row;

I don't need data that it's type id is different from 1,2,3

Expected result

CodePudding user response：

You can use row_number():

select t.*
from (select t.*,
             row_number() over (partition by id order by type_id) as seqnum
      from t
      where type_id in (1, 2, 3)
     ) t
where seqnum = 1;

Note: This uses the fact that the types you are looking for are ordered. If they are arbitrary, then you might need a case expression in the order by. For instance, if you wanted the precedence as 2, 1, 3:

order by (case type_id when 2 then 1 when 1 then 2 when 3 then 3 end)

CodePudding user response：

select id,
       min(name) keep (dense_rank first order by type_id) as name,
       min(type_id) as type_id
from   table_name
where  type_id in (1, 2, 3)
group  by id
;