Oracle SQL hierarchical query from bottom to top

Question

I have a table where I want to go from bottom to top using hierarchical queries.

The problem is that I need the get the value of one column from root (top) using CONNECT_BY_ROOT, but since I reverse the way the hierarchical query works (reverse the prior in connect by and the start with), this function (CONNECT_BY_ROOT) consider my 'start with' row as level 1 (root) then gets me this value.

In other words, I want a way to reverse the CONNECT_BY_ROOT to get me the value of a column from the last possible level and not the root.

+----+-----------+-------+
| ID | ID_PARENT | VALUE |
+----+-----------+-------+
|  1 |      null |     5 |
|  2 |         1 |     9 |
|  3 |         2 |  null |
+----+-----------+-------+

I want to get the value of ID = 1 (5) to the ID = 3 like this:

+----+-------+------------+
| ID | VALUE | VALUE_root |
+----+-------+------------+
|  1 |  5    |      5     |
|  2 |  9    |      5     | 
|  3 |  null |      5     |
+----+-------+------------+

I tried this but all I get is null as value_root:

SELECT id,
CONNECT_BY_ROOT VALUE as VALUE_root
FROM my_table
START WITH ID = 3
CONNECT BY ID = PRIOR ID_PARENT

EDIT: I forgot to mention that in my real system I'm dealing with millions of rows of data, the reason why I'm reversing the hierachical queries in first place is to make it better in terms of performance!

Answer 1

You may retrieve the root (which is a bottom node in your case) for all the tree upwards and then apply analytical function partitioned by the root to translate parent value to all the tree nodes. This is also possible for multiple nodes in start with.

with src (id, parentid, val) as (
  select 1, cast(null as int), 5 from dual union all
  select 2, 1, 9 from dual union all
  select 3, 2, null from dual union all
  select 4, 2, null from dual union all
  select 5, null, 10 from dual union all
  select 6, 5, 7 from dual
  
)
select
  connect_by_root id as tree_id
  , id
  , parentid
  , val
  , max(decode(connect_by_isleaf, 1, val))
      over(partition by connect_by_root id) as val_root
from src
start with id in (3, 4, 6)
connect by id = prior parentid

order by 1, 2, 3

TREE_ID	ID	PARENTID	VAL	VAL_ROOT
3	1	-	5	5
3	2	1	9	5
3	3	2	-	5
4	1	-	5	5
4	2	1	9	5
4	4	2	-	5
6	5	-	10	10
6	6	5	7	10

Answer 2

You were almost there

 SELECT id,
        value,
        CONNECT_BY_ROOT VALUE as VALUE_root
   FROM your_table
  START WITH ID = 1
CONNECT BY prior ID = ID_PARENT

Answer 3

You can try below query here I have just updated the START WITH condition and CONNECT BY clause -

SELECT id,
CONNECT_BY_ROOT VALUE as VALUE_root
FROM my_table
START WITH ID = 1
CONNECT BY PRIOR ID = ID_PARENT;

Fiddle Demo.

Answer 4

One posibility is to first perform a hierarchical query starting from the root - to get the root node for each row.

In the second step you perform the bottom up query (starting in all leaves nodes) and use the pre-calculated root node

Below the solution using Recursive Subquery Factoring

with hir (id, id_parent, value, value_root) as
(select id, id_parent, value, value value_root
from tab 
where id_parent is null
union all
select tab.id, tab.id_parent, tab.value, hir.value_root
from hir
join tab on tab.id_parent = hir.id
),
hir2 (id, id_parent, value, value_root) as 
(select id, id_parent, value, value_root from hir 
where ID in (select id from tab /* id of leaves */
             minus
             select id_parent from tab)
union all
select hir.id, hir.id_parent, hir.value, hir.value_root
from hir2
join hir on hir2.id_parent = hir.id 
)
select id,value, value_root  
from hir2
;

        ID      VALUE VALUE_ROOT
---------- ---------- ----------
         3                     5
         2          9          5
         1          5          5

Nte that the order of the row 3, 2, 1 is the bottom up order that you want, but fail to reach in your example output.