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

The best I came up with using Oracle 9 is this: select parent_id
FROM (select parent_id, RANK() OVER (ORDER BY level DESC) rnk from tbl_parent_child
start with child_id = 139527170 connect by child_id = prior parent_id ) where rnk=1 Received on Thu Oct 12 2006 - 14:40:44 CDT