Hierarchial Query to bring the root element for all leaf element-CodePudding

I have a table ABC with the following columns

AO	AOM
100	200
200	300
300	400
600	500
500	300
800	900
800	1000
900	1000
1200	1300
100	1200
1500	1600
100	1600

and if we see here we can see that 100 is the root element and has following leaf elements 200,300,400,500,600,1200,1300

I will need my output to look like below:

list of all the elements and the corresponding root element

ELEMENT	ROOT
100	100
200	100
300	100
400	100
500	100
600	100
1200	100
1300	100
800	800
900	800
1000	800
1500	100
1600	1600

I tried using the below query as a starting point but was not sure on how to get the expected value

select * from ABC start with ao=100 connect by ao = prior aom;

CodePudding user response：

It is easier to reverse the question and treat each element as the root and then navigate back up to the ancestors until you reach a leaf (an element with no children):

SELECT DISTINCT element, root
FROM   (
  SELECT CONNECT_BY_ROOT aom AS aom,
         CONNECT_BY_ROOT ao  AS ao,
         ao AS root
  FROM   abc
  WHERE  CONNECT_BY_ISLEAF = 1
  CONNECT BY PRIOR ao = aom
)
UNPIVOT (
  element FOR type IN (ao, aom)
);

Which, for the sample data:

CREATE TABLE abc (AO, AOM) AS
SELECT  100,  200 FROM DUAL UNION ALL
SELECT  200,  300 FROM DUAL UNION ALL
SELECT  300,  400 FROM DUAL UNION ALL
SELECT  600,  500 FROM DUAL UNION ALL
SELECT  500,  300 FROM DUAL UNION ALL
SELECT  800,  900 FROM DUAL UNION ALL
SELECT  800, 1000 FROM DUAL UNION ALL
SELECT  900, 1000 FROM DUAL UNION ALL
SELECT 1200, 1300 FROM DUAL UNION ALL
SELECT  100, 1200 FROM DUAL;

Outputs:

ELEMENT ROOT

100 100

200 100

300 100

500 600

300 600

400 100

400 600

600 600

800 800

900 800

1000 800

1200 100

1300 100

Note: 600 is a root because you have the rows in your data set as 600, 500 and 500, 300 and not 500, 600 and 300, 500. If the ao and aom values in those rows were reversed then the root would be 100 for all those rows.

You can start from the roots and work down to the descendants but its less efficient as you need a sub-query to find the roots:

SELECT DISTINCT
       element,
       root
FROM   (
  SELECT CONNECT_BY_ROOT ao AS root,
         ao,
         aom
  FROM   abc
  START WITH ao NOT IN (SELECT aom FROM abc)
  CONNECT BY PRIOR aom = ao
)
UNPIVOT (
  element FOR type IN (ao, aom)
)

The output is identical.

db<>fiddle here