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

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 12 Oct 2006 18:29:18 -0700
Message-ID: <1160702957.964452.250390@m73g2000cwd.googlegroups.com>

Mike C 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
>
> 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

Interesting problem, let's try an experiment to see if there are other solutions as well:
SELECT
  PARENT_ID,
  LEVEL POSITION
FROM
  TBL_PARENT_CHILD
CONNECT BY PRIOR
  PARENT_ID=CHILD_ID
START WITH
  CHILD_ID=139527170; Does the above return the correct parent-child relationship? If so, the goal then is to return the one row that has the highest level. This can be accomplished with inline views. The inner-most inline view in the following SQL statement creates the parent-child relationship. Moving out, the next inline view sorts the rows by the level, which I aliased to POSITION - this generates a usable ROWNUM which can be used to only return the one row of interest, with a simple WHERE clause.

SELECT
  *
FROM
  (SELECT
    *
  FROM
    (SELECT

      PARENT_ID,
      LEVEL POSITION
    FROM
      TBL_PARENT_CHILD
    CONNECT BY PRIOR
      PARENT_ID=CHILD_ID
    START WITH
      CHILD_ID=139527170)

  ORDER BY
    POSITION DESC)
WHERE
  ROWNUM=1; Let's try it from a different direction. What if I want to list the children and their parents in a single row? I could then look at the right-most column to determine the ultimate parent. From the same starting point, we search for the highest sorted PARENT_ID at each level, which will reduce the returned rows per level to 1. To then combine the results of the multiple rows into a single row, we can use a combination of MAX and DECODE, like this: SELECT

  MAX(DECODE(POSITION,1,PARENT_ID,NULL)) P1,
  MAX(DECODE(POSITION,2,PARENT_ID,NULL)) P2,
  MAX(DECODE(POSITION,3,PARENT_ID,NULL)) P3,
  MAX(DECODE(POSITION,4,PARENT_ID,NULL)) P4,
  MAX(DECODE(POSITION,5,PARENT_ID,NULL)) P5,
  MAX(DECODE(POSITION,6,PARENT_ID,NULL)) P6,
  MAX(DECODE(POSITION,7,PARENT_ID,NULL)) P7,
  MAX(DECODE(POSITION,8,PARENT_ID,NULL)) P8,
  MAX(DECODE(POSITION,9,PARENT_ID,NULL)) P9,
  MAX(DECODE(POSITION,10,PARENT_ID,NULL)) P10

FROM
  (SELECT
    MAX(PARENT_ID) PARENT_ID,
    POSITION
  FROM
    SELECT

      PARENT_ID,
      LEVEL POSITION
    FROM
      TBL_PARENT_CHILD
    CONNECT BY PRIOR
      PARENT_ID=CHILD_ID
    START WITH
      CHILD_ID=139527170)

  GROUP BY
    POSITION); OK, now to find the ultimate parent in a single column, allowing for up to 9 levels of children:
SELECT
  NVL(MAX(DECODE(POSITION,10,PARENT_ID,NULL)),     NVL(MAX(DECODE(POSITION,9,PARENT_ID,NULL)),

      NVL(MAX(DECODE(POSITION,8,PARENT_ID,NULL)),
        NVL(MAX(DECODE(POSITION,7,PARENT_ID,NULL)),
          NVL(MAX(DECODE(POSITION,6,PARENT_ID,NULL)),
            NVL(MAX(DECODE(POSITION,5,PARENT_ID,NULL)),
              NVL(MAX(DECODE(POSITION,4,PARENT_ID,NULL)),
                NVL(MAX(DECODE(POSITION,3,PARENT_ID,NULL)),
                  NVL(MAX(DECODE(POSITION,2,PARENT_ID,NULL)),
                    MAX(DECODE(POSITION,1,PARENT_ID,NULL))

)))))))))
FROM
  (SELECT
    MAX(PARENT_ID) PARENT_ID,
    POSITION
  FROM
    SELECT

      PARENT_ID,
      LEVEL POSITION
    FROM
      TBL_PARENT_CHILD
    CONNECT BY PRIOR
      PARENT_ID=CHILD_ID
    START WITH
      CHILD_ID=139527170)

  GROUP BY
    POSITION); Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Oct 12 2006 - 20:29:18 CDT