Re: hierarchical query problem

From: Haximus <e_at_t.me>
Date: Wed, 02 Mar 2005 22:48:16 GMT
Message-ID: <QorVd.23187$LN5.13183@edtnps90>

Cris Carampa wrote:
> I have a hierarchical query that returns the following output:
>
> id date
> ------------------------------ --------
> 18331 25-01-05
> ---13204 28-10-04
> ------13193 07-10-04
> ---13205 28-10-04
> ------13194 07-10-04
> ---13206 28-10-04
> 18332 25-01-05
> ---13204 28-10-04
> ------13193 07-10-04
> ---13205 28-10-04
> ------13194 07-10-04
> ---13206 28-10-04
> 19521 15-02-05
> ---14798 25-11-04
> ---15228 02-12-04
> ------15223 08-11-04
>
> Please notice that ID is formatted in order to show its hierarchical level.
>
> I wish to wrote another query that uses this query as an inline view and
> returns an output like this:
>
> root days
> ----- ----
> 18331 xxx
> 18332 yyy
> 19521 zzz
>
> Where 18331, 18332 and 19521 are the root nodes and "days" are the days
> between the date of the root node and the average date of the leaf nodes.
>
> Any suggestion?
>
> Thank you. Kind regards,

For grouping, each row needs to know it's root, then you can group by root id and do aggregate functions.

If you're on 9i you can duplicate CONNECT_BY_ROOT functionality with the following:

SELECT id, type FROM table
WHERE LEVEL = (

         SELECT MAX ( LEVEL )
         FROM table
         START WITH id = ?
         CONNECT BY PRIOR parent_id = id
     )

START WITH id = ?
CONNECT BY PRIOR parent_id = id Received on Wed Mar 02 2005 - 16:48:16 CST