clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335866 fiddles created (27453 in the last week).

create table foo( id integer primary key , parent_id integer references foo , bar char(1) );
 hidden batch(es)


insert into foo(id,parent_id,bar) select 1,null,'A' from dual union all select 2,1,'A' from dual union all select 3,2,'B' from dual union all select 4,1,'B' from dual union all select 5,4,'B' from dual union all select 6,4,'A' from dual union all select 7,1,'B' from dual union all select 8,7,'A' from dual union all select 9,8,'A' from dual union all select 10,9,'B' from dual;
10 rows affected
 hidden batch(es)


select * from foo where bar='B';
ID PARENT_ID BAR
3 2 B
4 1 B
5 4 B
7 1 B
10 9 B
 hidden batch(es)


select * from foo where bar='B' start with parent_id is null connect by parent_id=(prior id) and (prior bar)<>'B';
ID PARENT_ID BAR
3 2 B
4 1 B
7 1 B
 hidden batch(es)


with l as ( select foo.*, level lev from foo where bar='B' start with parent_id is null connect by parent_id=(prior id) and (prior bar)<>'B' order by level ) select * from l where lev=(select lev from l where rownum=1);
ID PARENT_ID BAR LEV
4 1 B 2
7 1 B 2
 hidden batch(es)