Hierarchical Queries - Oracle

Question

I have a table with many project id's and for each project ,I have hierarchical structure as below.

My input is risk id..How can I get the top level parent for the input risk ID.

IF I give 25 as Input I should get 23 as output.how can I achieve this.

Answer 1

Starting from the required row and ascending to the root:

SELECT RiskID,
FROM   PROJECT_RISKS
WHERE  CONNECT_BY_ISLEAF = 1
START WITH
       RiskID    = 25
CONNECT BY
       PRIOR Link2 = Link1
AND    PRIOR ProjectID = ProjectID;

Starting from the root and descending to the required row:

SELECT  CONNECT_BY_ROOT( RiskID ) AS RiskID
FROM    Project_Risks
WHERE   RiskID    = 25
START WITH
        link2     IS NULL
CONNECT BY
       PRIOR Link1 = Link2
AND    PRIOR ProjectID = ProjectID;

Both will give the same answer.