How can I find the highest parent level or field in Oracle?

Question

I have a table called "test". It contains Parent and Child columns.

Parent  Child
  1      101
  1      102
  1      103
 101     121
 101     123
 103     133
 121     80
 121     81

Now, I wrote a query which brings me all the child/parents for the given "1" value.

( SELECT parent,child,LEVEL FROM TEST
    START WITH child= 1
    CONNECT BY PRIOR parent=child) 
UNION ALL
   (SELECT parent,child,LEVEL FROM TEST
      START WITH parent=1
      CONNECT BY PRIOR child=parent)
      ORDER BY parent

But I want all the child/parent in that hierarchy irrespective of any value given ..i.e. if I give "80" ...., I have to fetch HIGHEST PARENT VALUE( in this case "1") and then search all the children of that highest parent value below.

I'm having hard time to understand your needs. Are you looking for _"all children having a common ancestor"_ ? — Sylvain Leroux, Oct 03 '14 at 19:56
What make `1` the "HIGHEST PARENT VALUE" of `80`? Is it because actually it's the lowest value in an ascending ordered list? — Caffé, Oct 03 '14 at 19:59
@Caffé As far as I understand, `1` is the ultimate ancestor of `80`: `80 => 121 => 101 => 1`. But it's not clear what should be done after that. Find all descendants of `1`? Or only its "direct" children? — Sylvain Leroux, Oct 03 '14 at 20:07
Yes, all the descendants of 1.... ..first got to find 1 and then get ALL the children of 1 i.e. 1 --> 101, 102,103,and then for 101 -> 121,123 and 103-->133 and for 121-->80,81. Not sure, if this is possible... to get entire hierarchy if given any value associated to it. — Anitha, Oct 04 '14 at 00:31

score 3 · Accepted Answer · answered Oct 04 '14 at 13:44

You could use a recursive query and the pseudo-column CONNECT_BY_ISLEAF to find the ultimate ancestor of a node:

SELECT parent AS ancestor 
  FROM X 
  WHERE CONNECT_BY_ISLEAF <> 0
  START WITH child=80 
  CONNECT BY PRIOR parent=child
--           ^^^^^^^^^^^^^^^^^^
--             ascendant order

Once you have the "ultimate ancestor", a second recursive query will get all the descendants from that node:

SELECT child AS descendant, LEVEL
  FROM X
  START WITH parent=1 
  CONNECT BY PRIOR child=parent
--           ^^^^^^^^^^^^^^^^^^
--            descendant order

Please note the LEVEL pseudo-column that allow you to know the "distance" between a node and its ultimate ancestor.

Putting it all together:

SELECT child AS descendant, LEVEL
  FROM X
  START WITH parent IN (

    SELECT parent AS ancestor
      FROM X 
      WHERE CONNECT_BY_ISLEAF <> 0
      START WITH child=80 
      CONNECT BY PRIOR parent=child

  ) 
  CONNECT BY PRIOR child=parent

Producing, given your sample data:

DESCENDANT  LEVEL
101         1
121         2
80          3
81          3
123         2
102         1
103         1
133         2

I have researched and found that there is no Connect By clause in Teradata. If we want to do the same thing in Teradata, what should be my approach for the same question? Use of Recursive Factor Query.... how can I do that? — Anitha, Oct 06 '14 at 19:09
@Anitha This will deserve _an other question_. That way you could have an answer from someone that really know [tag:Teradata]. — Sylvain Leroux, Oct 07 '14 at 10:37

How can I find the highest parent level or field in Oracle?

1 Answers1