Re: CONNECT BY / START WITH query to count "families"

trippknightly_at_hotmail.com wrote:
> Suppose I have a table that contains Account# & RelatedAccount# (among
> other things).
>
> How could I use CONNECT BY & START WITH in a query to count
> relationships or families.
>
> For example, in
>
> ACCT REL_ACCT
> Bob Mary
> Bob Jane
> Jane Bob
> Larry Moe
> Curly Larry
>
> there are 2 relationship sets (Bob,Mary,Jane & Larry,Moe,Curly). If I
> added
>
> Curly Jane
>
> then there'd be only 1 larger family. Can I use CONNECT BY & START
> with to detect such relationships and count them? In my case I'd be
> willing to go any number of levels deep in the recursion. Am open to
> other suggestions as well.

create table input as (
select 1 x, 2 y from dual
union
select 2, 3 from dual
union
select 2, 4 from dual
union
select 6, 7 from dual
union
select 8, 9 from dual);

with OTC as ( -- Ordered transitive closure  select connect_by_root(x) x, y,

        sys_connect_by_path('['||x||','||y||')','->') path from (   select * from (
   select connect_by_root(x) x, y from (     select x,y from input
    union
    select y,x from input
   ) connect by nocycle x = prior y
  ) where x < y
 ) connect by nocycle x = prior y
) select * from OTC o
where x not in (

          select x from OTC oo where length(oo.path)>length(o.path) ) and y not in (

          select y from OTC oo where length(oo.path)>length(o.path) );

This is supposed to return paths identifying grath connected components like like this
[1,2)->[2,3)->[3,4)
[6,7)
and the rest should be easy. Unfortunately, it 600ed in 10.2, so I was unable to proceed. Received on Thu Sep 29 2005 - 11:30:34 CDT