Return row per key based on precedence in Oracle’s SQL

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

id	name	type_id
23	xx	1
24	yy	1
24	yy	2
24	yy	3
25	zz	2
26	qq	2
26	qq	3
27	ww	null
28	tt	4
28	rr	5

Expected result

id	name	type_id
23	xx	1
24	yy	1
25	zz	2
26	qq	2

Answer

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)