Grouping sibling relationships together into family groups - oracle sql

We have a table which records relationships between siblings – tbl_relationships (fig1 of attached)

The reverse of each relationship is listed so each relationship appears twice. Eg. John > Paul, and Paul > John

I need a column that generates the unique id of the family (as per fig2 of attached).

Is this possible? Any help to develop this sql is appreciated.
family-code-4.xlsx
tonMachine100Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
if you don't care which family_ids are associated with which groups
you can try this...


If you do care,  what are the rules for determining the ordering?
That is,  why are the Beatles  family_id 1 and not 2, 3 or 4?


SELECT rel_id,
       rel_from_id,
       rel_from_name,
       rel_to_id,
       rel_to_name,      
       DENSE_RANK() OVER (ORDER BY grp) family_id
  FROM (SELECT r1.*,
               r2.grp,
               ROW_NUMBER() OVER(PARTITION BY r1.rel_id ORDER BY LENGTH(grp) DESC, grp) rn
          FROM tbl_relationships r1,
               (    SELECT SYS_CONNECT_BY_PATH(rel_from_id, ',') || ',' grp
                      FROM tbl_relationships r
                CONNECT BY NOCYCLE PRIOR rel_to_id = rel_from_id AND rel_from_id > PRIOR rel_from_id)
               r2
         WHERE r2.grp LIKE '%,' || r1.rel_from_id || ',%')
 WHERE rn = 1
sdstuberCommented:
just taking a guess - if the family_ids should be generated in order based on the order of lowest rel_id of each family,  then try this...



   SELECT rel_id,
         rel_from_id,
         rel_from_name,
         rel_to_id,
         rel_to_name,
         DENSE_RANK() OVER (ORDER BY grp_order) family_id
    FROM (SELECT r1.*,
                 MIN(rel_id) OVER (PARTITION BY r2.grp) grp_order,
                 ROW_NUMBER() OVER(PARTITION BY r1.rel_id ORDER BY LENGTH(grp) DESC, grp) rn
            FROM tbl_relationships r1,
                 (    SELECT SYS_CONNECT_BY_PATH(rel_from_id, ',') || ',' grp
                        FROM tbl_relationships r
                  CONNECT BY NOCYCLE PRIOR rel_to_id = rel_from_id
                                 AND rel_from_id > PRIOR rel_from_id) r2
           WHERE r2.grp LIKE '%,' || r1.rel_from_id || ',%')
   WHERE rn = 1
ORDER BY rel_id;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tonMachine100Author Commented:
works perfectly. thanks.

though i'll have to do some reading into Hierarchical Queries to understand whats happening here!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.