Search code examples
sqloraclehierarchy

Find the top-level parent group each record belongs to

I have hierarchical data with the following structure. The levels are 1 to 6, so instead of doing 6 joins and then coalesce, how can I find what is the top-level parent (which has no parent_nr).

enter image description here

I've tried the accepted answer here,

SELECT
  aa.code_nr,
  aa.parent_nr,
  CONNECT_BY_ROOT aa.code_nr AS "Top Level ID"
FROM mytable_t aa
CONNECT BY PRIOR aa.code_nr = aa.parent_nr
;

but it only gives me the next level as "Top Level ID" and not the final level (A)

enter image description here

Solution

  • Oracle Setup:

    CREATE TABLE my_table ( code_nr, parent_nr ) AS (
  SELECT 'A', NULL FROM DUAL UNION ALL
  SELECT 'A.1', 'A' FROM DUAL UNION ALL
  SELECT 'A.1.1', 'A.1' FROM DUAL UNION ALL
  SELECT 'A.1.1.1', 'A.1.1' FROM DUAL UNION ALL
  SELECT 'A.1.1.2', 'A.1.1' FROM DUAL UNION ALL
  SELECT 'A.1.1.1.1', 'A.1.1.1' FROM DUAL UNION ALL
  SELECT 'A.1.1.2.1', 'A.1.1.2' FROM DUAL UNION ALL
  SELECT 'A.1.1.2.2', 'A.1.1.2' FROM DUAL;

    Query:

    SELECT LEVEL,
       code_nr AS root_code_nr,
       CONNECT_BY_ROOT( code_nr ) AS code_nr
FROM   my_table
WHERE  CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR parent_nr = code_nr;

    Output:

    LEVEL ROOT_CODE CODE_NR 
----- --------- ---------
    1 A         A        
    2 A         A.1      
    3 A         A.1.1    
    4 A         A.1.1.1  
    5 A         A.1.1.1.1
    4 A         A.1.1.2  
    5 A         A.1.1.2.1
    5 A         A.1.1.2.2