One example about hierarchies

Today morning I just received a question from a friend where I used hierarchies :


> Let's assume a couple of persons have bought some cakes togeher and they want to eat it:
>
> Create table cake_owners
> (owner# number,
> cake# number,
> constraint cake_pk primary key (owner#,cake#)
> using index);
>
> insert into cake_owners values (1,100);
> insert into cake_owners values (1,200);
> insert into cake_owners values (2,200);
> insert into cake_owners values (2,300);
> insert into cake_owners values (3,300);
> -----
> insert into cake_owners values (4,500);
> -----
> insert into cake_owners values (6,600);
> insert into cake_owners values (7,600);
> -----
> commit;
>
> So owner 1 owns cake 100 and a part of cake 200. Owner 2 owns a part of cake 200 and a part of cake 300 where the reset is owned by 3.
> Owner 4 owns cake 500 alone and cake 600 is owned by 2 persones 6 and 7.
>
> Now I want to place all owners on one table who share parts of their cake so that all cakes can be eaten compleatly without leaving the table.
> The table must be as small as possible and I want to know how many tables are needed and how big each one must be, or who is sitting at it.
> Of course a person can sit only at one table.
>
> In this much simplyfied example I need 3 tables the biggest one needs 3 chairs.

This is typically solved with PL/SQL, but with plain SQL, I need a hierachy (cake=prior cake and owner<>prior owner) or (cake<>prior cake and owner=prior owner). This is going to loop, with 10g I will use nocycle. With connect by root and count, I will found out the table master with the most guests.

select dense_rank() over (order by rootowner) tableno, owner#
from (
 select owner#,
  row_number() over
   (partition by owner# order by owner_c, rootowner) r,
  rootowner
 from (
  select
   rootowner,
   count(distinct owner#) over
    (partition by rootowner) owner_c,
   owner#
  from (
   select
    owner#, cake#,
    connect_by_root owner# rootowner
   from
    cake_owners
   connect by nocycle
    (owner#<>prior owner# and cake#=prior cake#)
    or
    (owner#=prior owner# and cake#<>prior cake#)
   )
  )
 ) where r=1
order by tableno, owner#;

TABLENO OWNER#
------- ------
1       1
1       2
1       3
2       4
3       6
3       7