with t(a, b, c) as (select 11, '20120201', 'T' from dual union all select 11, '20120202', 'C' from dual union all select 11, '20140405', 'R' from dual union all select 11, '20140905', 'P' from dual union all select 22, '20130601', 'T' from dual union all select 22, '20130408', 'C' from dual union all select 22, '20140609', 'R' from dual ) select t1.* from t t1 , t t2 where 1=1 and t1.a = t2.a and t1.b > t2.b and t1.c != 'T' and t2.c = 'T' ; select a, b, c from ( select t.*, level as lv from t where c != 'T' start with c = 'T' connect by nocycle prior a = a and prior b < b ) where lv = 2 ;