Re: Efficiently getting top level parent in hierarchy with connect by

Mike C wrote:
> I have a query on a simple table that has parent child relationships.
> I need a query to get the highest level parent for a child. Is there a
> simpler way to do this than what I have which repeats the query to get
> the max level?
>
> select parent_id,level from tbl_parent_child g
> WHERE level=(SELECT MAX(level) FROM tbl_parent_child start with
> child_id = 139527170 connect by child_id = prior parent_id )
> start with child_id = 139527170 connect by child_id = prior parent_id

Look at the syntax for CONNECT BY in Morgan's Library at www.psoug.org. Look at the START WITH demo and it's use with CONNECT BY PRIOR. Also look at CONNECT_BY_ROOT and the LEVEL pseudocolumn.

That is not to say they are going to be more efficient but rather that they are options to explore.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org