Great!
Thank you.
Andy Hassall wrote:
> On 23 Aug 2006 14:08:36 -0700, artmt_at_hotmail.com wrote:
>
> >Consider the following example.
> >
> >A is the parent of B. End of tree.
> >C and D are both parents of E. E is the parent of F. End of tree.
> >
> >These relationsips are recorded in Tab1 as:
> >
> >Entity Parent
> >------ ------
> >A
> >B A
> >C
> >D
> >E C
> >E D
> >F E
>
> >I need to generate the data set that shows top level ultimate parent
> >for each entity:
> >
> >Entity Ult_Parent
> >------ ----------
> >A A
> >B A
> >C C
> >D D
> >E C
> >E D
> >F C
> >F D
> >
> >What is the best way to write this query, without building temp tables?
>
> SQL> select * from t;
>
> ENTITY PARENT
> ------ ------
> A
> B A
> C
> D
> E C
> E D
> F E
>
> 7 rows selected
>
> SQL> select entity, connect_by_root entity ult_parent
> 2 from t
> 3 connect by prior entity = parent
> 4 start with parent is null
> 5 order by entity;
>
> ENTITY ULT_PARENT
> ------ ----------
> A A
> B A
> C C
> D D
> E C
> E D
> F D
> F C
>
> 8 rows selected
>
> --
> Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
> http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
Received on Thu Aug 24 2006 - 09:17:16 CDT
