Search code examples
sqloracle-databasegreatest-n-per-group

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

Solution

  • 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)