Re: Hierarchical query

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