Re: CONNECT BY / START WITH query to count "families"
- From: mikharakiri_nospaum@xxxxxxxxx
- Date: 29 Sep 2005 09:30:34 -0700
trippknightly@xxxxxxxxxxx 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.
.
- References:
- CONNECT BY / START WITH query to count "families"
- From: trippknightly
- CONNECT BY / START WITH query to count "families"
- Prev by Date: Re: CONNECT BY / START WITH query to count "families"
- Next by Date: Re: A very slow Select Statement
- Previous by thread: Re: CONNECT BY / START WITH query to count "families"
- Next by thread: Video training for PL/SQL ?
- Index(es):
Relevant Pages
|
