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 ownerprior owner) or (cakeprior 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