Re: Order in a hierarchical select

>Problem description:
>
>I want to have an order by name within a hierarchical select:
>SELECT -1, Level, Name, null, to_char(ID)
> FROM TABLE
> CONNECT BY PRIOR ID=CHILD_ID
> START WITH ID IN
> (SELECT ID
> FROM TABLE
> WHERE CHILD_ID IS NULL);
>
>
>The original table content is:
>
>ID Name Child_ID
>-----------------------
>1 a 3
>2 b 4
>3 d null
>4 c null
>5 f 4
>6 e 4
>
>The hierarchical select result should look like:
>
>c
> |-b
> |-e
> |-f
>d
> |-a
>

That's correct - can't do a ORDER BY in a CONNECT BY...

I had a similar situation; the only way I could get the desired results was to add a new column which was the concatenation of the parents (the part you want to see/order by), populate it through triggers (statement, not row triggers) that programatically walked the tree. Then I could sort on this column, without needing to do the tree-walk. I think I also kept the number of levels.

 Let us know if you find another way...

Dan Hekimian-WIlliams Received on Fri Dec 17 1999 - 03:15:59 CET