Finding last child in a hierarchy in oracle

Question

I have a table like

prnt_id   child_id
-----------------
1           2
2           4
3
5
6           7
7           8
8           9

My result should contain

prnt_id  child_id
-----------------
1          4
3
5
6          9

I am using oracle 10g.

I tried doing it with connect by but did not get expected result.

Answer 1

Use CONNECT_BY_ROOT to get the prnt_id from the root and CONNECT_BY_ISLEAF to indicate, wherever this is the leaf node. Something like that should work:

SELECT PRNT_ID, 
       CHILD_ID
FROM (SELECT CONNECT_BY_ROOT PRNT_ID PRNT_ID, CHILD_ID, CONNECT_BY_ISLEAF leaf 
      FROM TABLE
      CONNECT BY PRIOR CHILD_ID = PRNT_ID
      START WITH prnt_id NOT IN (SELECT CHILD_ID
                                 FROM TABLE
                                 WHERE CHILD_ID IS NOT NULL))
WHERE leaf = 1

For example, this will give you the desired result:

WITH dat AS (SELECT 1 prnt_id, 2 child_id FROM DUAL
             UNION ALL
             SELECT 2 prnt_id, 4 child_id FROM DUAL
             UNION ALL
             SELECT 3 prnt_id, NULL child_id FROM DUAL
             UNION ALL
             SELECT 5 prnt_id, NULL child_id FROM DUAL
             UNION ALL
             SELECT 6 prnt_id, 7 child_id FROM DUAL
             UNION ALL
             SELECT 7 prnt_id, 8 child_id FROM DUAL
             UNION ALL             
             SELECT 8 prnt_id, 9 child_id FROM DUAL)
SELECT PRNT_ID, 
       CHILD_ID
FROM (SELECT CONNECT_BY_ROOT PRNT_ID PRNT_ID, CHILD_ID, CONNECT_BY_ISLEAF leaf FROM dat
      CONNECT BY PRIOR CHILD_ID = PRNT_ID
      START WITH prnt_id NOT IN (SELECT CHILD_ID
                                 FROM dat
                                 WHERE CHILD_ID IS NOT NULL))
WHERE leaf = 1


PRNT_ID CHILD_ID
1       4
3   
5   
6       9