Hierarchical query

From: <artmt_at_hotmail.com>
Date: 23 Aug 2006 14:08:36 -0700
Message-ID: <1156367316.661939.325070@74g2000cwt.googlegroups.com>

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
Received on Wed Aug 23 2006 - 16:08:36 CDT