select max(t2.a) amax,t2.b from ( select t.a,t.b from t,(select b,max(a) amax from t where b=2 group by b) t1 where t.b=1 and t.a>t1.amax) t2 group by t2.b;
with t1 as ( select 1 c1,2 c2 from dual union all select 3 c1,1 c2 from dual union all select 4 c1,2 c2 from dual union all select 6 c1,1 c2 from dual )select min(c1) c1 from t1 where c2 = 1 and c1 > (select max(c1) from t1 where c2 = 2) c1 ------------------ 1 6
select max(t2.a) amax,t2.b from (
select t.a,t.b from
t,(select b,max(a) amax from t where b=2 group by b) t1
where t.b=1 and t.a>t1.amax) t2
group by t2.b;
with t1 as
(
select 1 c1,2 c2 from dual
union all
select 3 c1,1 c2 from dual
union all
select 4 c1,2 c2 from dual
union all
select 6 c1,1 c2 from dual
)select min(c1) c1
from t1
where c2 = 1 and c1 > (select max(c1) from t1 where c2 = 2)
c1
------------------
1 6