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

I believe connect_by_root is only available in 10g. I am using 9.

I really do not care about efficiency of performance. I am looking to simplify the query so I don't have to repeat the connect by select.

DA Morgan wrote:
> 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
Received on Thu Oct 12 2006 - 10:54:16 CDT