Get the chains and highest parent, in a hierarchy of organisations

I have the following data in TABLE_A, where ORG_1 is the parent of ORG_2:

ORG_1  ORG_2
01     02
02     03
02     04
05     06

So, org 01 is the parent of org 02, and org 02 is the parent of 03 and 04. Org 5 is the parent of only org 06.

I need to have unique names/numbers for the chains, and get reported the highest parent in the chain. Chain I define as 'all organisations that are related to each other'.

This is the desired result:

Chain  ORG_1  ORG_2 Highest_Parent_In_Chain
1      01      02    01
1      02      03    01
1      02      04    01
2      05      06    05

Chain=1 has a tree structure starting from ORG_1=01. Chain=2 has it's own chain.

I found some info about CONNECT BY, CONNECT BY PRIOR and CONNECT_BY_ROOT, but I don't get it working. Does anyone has an idea how to achieve this with a query in Oracle?

Solution

The chain number can be created with the analytic DENSE_RANK() function.

The highest parent in chain is a feature of hierarchical queries: the function CONNECT_BY_ROOT().

Your hierarchical table is non-standard - in a standard arrangement, the top levels (organizations 01 and 05) would also have a row where they appear as ORG_2, with NULL as ORG_1. That way the highest levels in the hierarchy are very easy to find: just look for ORG_1 IS NULL. As it is, the START WITH clause is more complicated, because we must find the tops first. For that we look for values of ORG_1 that do not also appear in ORG_2. That is the work done in the subquery in the START WITH clause.

with
     table_a ( org_1, org_2 ) as (
       select '01', '02' from dual union all
       select '02', '03' from dual union all
       select '02', '04' from dual union all
       select '05', '06' from dual
     )
-- End of simulated input data (for testing purposes only).
-- Solution (SQL query) begins BELOW THIS LINE.
select     dense_rank() over (order by connect_by_root(org_1)) as chain,
           org_1, org_2,
           connect_by_root(org_1) as highest_parent_in_chain
from       table_a
connect by org_1 = prior org_2
start with org_1 in 
                 ( select org_1 from table_a a
                   where not exists (select * from table_a where org_2 = a.org_1)
                 )
;     

CHAIN  ORG_1  ORG_2  HIGHEST_PARENT_IN_CHAIN
-----  -----  -----  -----------------------
    1  01     02     01
    1  02     03     01
    1  02     04     01
    2  05     06     05