Hierarchical query

Giganews Newsgroups
Subject: Hierarchical query
Posted by:  art…@hotmail.com
Date: 23 Aug 2006

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

The CONNECT BY query correctly returns 3 hierarchies:

Entity    Parent    Level
------    ------    -----
A        1
B    A    2
C        1
E    C    2
F    E    3
D        1
E    D    2
F    E    3

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?

Thanks
-Art

Replies