Extract only single value from multiple rows based on certain condition for same id in Oracle

oracle

I'm an Oracle beginner and I'm having troubleshooting the issue below.

My table:

+-------+------+-------+------ +
| ID    | GRP  | ITDESC| DEN   |  
+-------+------+-------+------ +
| 12345 | MANX | Apple | SendV | 
+-------+------+-------+------ +
| 12345 | MANX | Apple | Manual| 
+-------+------+-------+-------
| 12345 | MANX | Apple | UnVeri| 
+-------+------+-------+------ +
| 12346 | MANX | Mango | UnVeri| 
+-------+------+-------+------ +
| 12347 | MANX | PineAp| SendV| 
+-------+------+-------+------ +
 12348  | MANX | Pine | Manual|

I am expecting:

+-------+------+-------+------ +
| ID    | GRP  | ITDESC| DEN   |  
+-------+------+-------+------ +
| 12345 | MANX | Apple | SendV | 
+-------+------+-------+------ +
| 12346 | MANX | Mango | UnVeri| 
+-------+------+-------+------ +
| 12347 | MANX | PineAp| SendV| 
+-------+------+-------+------ +
| 12348  | MANX | Pine | Manual|
+-------+------+-------+------ +

I have multiple rows that have only the DEN column different for the same id. My aim is for the same id perform the check. If the value of DEN is 'Manual' then check to see if there is 'SendV' in DEN column for that id. If it is present then consider 'SendV' otherwise consider 'Manual'.
Note in the provided example, the order is random. The SendV can be in 2nd row or 3rd row or 4th row based on the action, however the text of DEN will be same as said above.

select * from table t1 where DEN IN ('Manual', 'SendV').

I am not aware how to write the condition?

Best Answer

So what happens then there is no row with Manual, but there is with SendV? Should it be SendV as in your example output?

with data as 
(
  select 12345 as id, 'MANX' as grp, 'Apple' as itdesc, 'SendV' as den from dual union all
  select 12345 as id, 'MANX' as grp, 'Apple' as itdesc, 'Manual' as den from dual union all
  select 12345 as id, 'MANX' as grp, 'Apple' as itdesc, 'UnVeri' as den from dual union all
  select 12346 as id, 'MANX' as grp, 'Mango' as itdesc, 'UnVeri' as den from dual union all
  select 12347 as id, 'MANX' as grp, 'PineAp' as itdesc, 'SendV' as den from dual union all
  select 12348 as id, 'MANX' as grp, 'Pine' as itdesc, 'Manual' as den from dual
)
select id, grp, itdesc, den from 
(
  select
    id, grp, itdesc, den, 
    row_number() over (partition by id, grp, itdesc order by 
      case grp when 'SendV' then 1 when 'Manual' then 2 else 3 end) as rn
  from data
)
where rn = 1
order by id;

        ID GRP  ITDESC DEN   
---------- ---- ------ ------
     12345 MANX Apple  SendV 
     12346 MANX Mango  UnVeri
     12347 MANX PineAp SendV 
     12348 MANX Pine   Manual
Related Question