Hierarchical Query connect by using start with clause

Question

The data I am working with is sequenced like the below example:

Example

What I want to know is this:

Want

What I get from my query is this (the actual query with actual data is posted at the end of this post) :

Get

This is the code I have now, which I feel is close to giving me what I want.

select * from ( select distinct id , idnew , CONNECT_BY_ROOT idlast , CONNECT_BY_ISLEAF "IsLeaf" , CONNECT_BY_ISCYCLE iscycle , level seq_order from mytable tbl1 connect by NOCYCLE idnew = prior id start with not exists (select 1 from mytable tbl2 where tbl2.itemloadid = tbl.itemloadidnew) ) abc order by abc.idlast, seq_order desc

The output I get from this code is this:

Output

How do I make sure that the first value of my sequences are interpreted as the roots (and not the leaves as today)? As I understand it, if they are interpreted as roots I can print a column with id_first, and not the id_last I have today.

Help is much appreciated! :)

Answer 1

You have to invert connect by clause:

select t.*, connect_by_root(id) id_first
  from mytable t 
  start with not exists (select 1 from mytable x where x.id_new = t.id)
  connect by id = prior id_new

^{dbfiddle demo}