CODEWITHSUNDEEP
sqloracle-databaserecursionparent-childhighest
Anitha
Anitha

Reputation: 77

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

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.

Upvotes: 0

Views: 5061

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

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

Upvotes: 3

Related Questions