oracle 9i get highest member of tree with given child

I have a parent-child relationship in an Oracle 9i database-table

like:

parent | child  
1      | 2  
2      | 3
2      | 4
null   | 1
1      | 8

I need to get the absolute parent from a given child. Say, I have child 4, it has to give me parent: 1

I already looked to CONNECT BY , but I can't find the solution.

From stackoverflow

• SELECT  parent
  FROM    (
          SELECT  parent
          FROM    (
                  SELECT  parent, level AS l
                  FROM    mytable
                  START WITH
                          child = 4
                  CONNECT BY
                          child = PRIOR parent
                  )
          ORDER BY
                  l DESC
          )
  WHERE   rownum = 1
  

  This will give you NULL as the absolute parent.

  If you want 1, replace parent with child:

  SELECT  child
  FROM    (
          SELECT  child
          FROM    (
                  SELECT  child, level AS l
                  FROM    mytable
                  START WITH
                          child = 4
                  CONNECT BY
                          child = PRIOR parent
                  )
          ORDER BY
                  l DESC
          )
  WHERE   rownum = 1
  

  jwdehaan : I need the absolute parent, no predefined number of levels.

  Quassnoi : This query gives the absolute parent (`NULL` is this case). If you want `1`, just replace `parent` with the `child` in the query above.

• Hi jwdehaan,

  you could use a CONNECT BY query to build the list of parents and then filter :

  SQL> WITH tree AS (
    2     SELECT 1 parent_id, 2 child_id FROM DUAL
    3     UNION ALL SELECT 2   , 3  FROM DUAL
    4     UNION ALL SELECT 2   , 4  FROM DUAL
    5     UNION ALL SELECT null, 1  FROM DUAL
    6     UNION ALL SELECT 1   , 8  FROM DUAL
    7  )
    8  SELECT child_id
    9    FROM (SELECT *
   10            FROM tree
   11          CONNECT BY PRIOR parent_id = child_id
   12           START WITH child_id = 4)
   13   WHERE parent_id IS NULL;
  
    CHILD_ID
  ----------
           1