--1
select *
from (select max(decode(type, 'yes', 1, 2)) over(partition by id_number) mm,
t.*
from t)
where mm = 1;
--2
select *
from (select max(decode(type, 'no', 1, 2)) over(partition by id_number) mm,
t.*
from t)
where mm = 1;
--3
select *
from (select count(distinct type) over(partition by id_number) cnt, t.*
from t)
where cnt = 2;
|