Get the top most element for any element in chain (tree) in Oracle Sql

Question

Consider the following structure with some nodes:

        1
        /\
       2  3
       |  |
       4  5
       |  |
       6  7   
       |
       8

or

with mydata as (
 select 8 id ,6 id_before, 400 datum from dual union all
 select 6,4, 300 from dual union all
 select 4, 2, 200 from dual union all
 select 2,1, 10 from dual union all
 select 3, 1, 60 from dual union all
 select 5, 3, 800 from dual union all
 select 7, 5, 900 from dual 
)

Now given the id of the node I want to get the root node id

e.g. for node 7 root note 1, for 5 root node 1 for 4 root node 1 etc.

I tried something like this:

select id, id_before,datum, SYS_CONNECT_BY_PATH(id_before,'/') as path,
SYS_CONNECT_BY_PATH(datum,'/') as datapath,
level,
CONNECT_BY_ROOT id_before
     from mydata
       where id=7
      connect by  id_before = prior id

with disappointing results:

7   5   900 /1/3/5  /60/800/900 3   1
7   5   900 /3/5    /800/900    2   3
7   5   900 /5      /900        1   5

Any ideas on how to fix this?

Thanks.

Answer 1

You can use CONNECT_BY_ISLEAF as a filter to find the root element:

Oracle Setup:

CREATE TABLE mydata ( id, id_before, datum ) as
 select 8, 6, 400 from dual union all
 select 6, 4, 300 from dual union all
 select 4, 2, 200 from dual union all
 select 2, 1,  10 from dual union all
 select 3, 1,  60 from dual union all
 select 5, 3, 800 from dual union all
 select 7, 5, 900 from dual;

Query:

SELECT CONNECT_BY_ROOT( id ) AS id,
       id_before AS root_id,
       SYS_CONNECT_BY_PATH( id, ',' ) || ',' || id_before AS path
FROM   mydata
WHERE  CONNECT_BY_ISLEAF = 1
CONNECT BY id = PRIOR id_before;

Output:

ID | ROOT_ID | PATH      
-: | ------: | :---------
 2 |       1 | ,2,1      
 3 |       1 | ,3,1      
 4 |       1 | ,4,2,1    
 5 |       1 | ,5,3,1    
 6 |       1 | ,6,4,2,1  
 7 |       1 | ,7,5,3,1  
 8 |       1 | ,8,6,4,2,1

db<>fiddle here