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

Question

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?

Answer 1

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

• Diya: This helps!! Thanks a ton!! Yes if there is no manual then it should send SendV.