0

I have a oracle table which looks like this:

Schema:

(number) node_id

(number) parent_id

(number) parent_seq

Each entry in the table represents a single parent/child relationship. A parent can be the parent for multiple children and a child can have multiple parents (but we can assume that no cycles exist because that is validated before committing). If a child has multiple parents then it will have more than one row corresponding to its node_id in the table, and the parent_seq will increment for each parent.

Now, I dont need to rebuild the entire tree, I just need to know the DEPTH of each node. DEPTH as following the common definition of (What is the difference between tree depth and height?)

The depth of a node is the number of edges from the node to the tree's root node.

Is there a way to do this gracefully in Oracle using the CONNECT_BY syntax?

Community
  • 1
  • 1
Monolithguy
  • 313
  • 6
  • 17

2 Answers2

2
select
   node_id,
   min(level) as best_level,
   min(sys_connect_by_path(node_id, '/')) 
      keep (dense_rank first order by level) 
      as best_path
from t
start with parent_id is null
connect by prior node_id = parent_id
group by node_id
order by 1

fiddle

Egor Skriptunoff
  • 23,359
  • 2
  • 34
  • 64
1

I believe I found the answer, which was in the documentation. The keyword "LEVEL" is a column which shows the node level when doing a connect_by statement. So you simply need the maximum level for a given node:

select node_id, max(LEVEL) from node_parent_link CONNECT BY PRIOR node_id = parent_id group by node_id

Monolithguy
  • 313
  • 6
  • 17