Re: ORDER BY issue with CONNECT BY query. Please help.

CONNECT BY does not sort results, though the order in which your result set is output is the write order from the table. (in 8.1.5 at least). There are two solutions for your problem, a kludge and an solid one.

Kludge: insert your records into a temporary table in the order you want to display them, and run your CONNECT BY on that. It works with my version, but there are no guarantees for future versions.

Solid solution: write a function which takes a node in the hierarchy returns a value on which you can sort. The function will contain a cursor whose query returns the parents of your node, and which you can concatenate into a sortable value. I use the RAWTOHEX function on the sort value, and concatenate the results.

"Jürgen Kärner" <j.karner_at_optiva.ee> wrote in message news:3b1f411a$0$266$ba624cac_at_news.uninet.ee...
> I have a connect by query.
> I want to order items WITHIN EACH LEVEL by name.
>
> How can I do this?
>
> x)
> Simple example of results:
>
> SMITH
> TIGER
> SIMON
> TACKER
> FORRESTER
> KELLY
> JACKSON
> KING
>
> x)
> If I include ORDER BY in the end of the statement, I get this:
>
> FORRESTER
> JACKSON
> KELLY
> KING
> SIMON
> SMITH
> TACKER
> TIGER
>
> x)
> What I want, is to order items by name WITHIN EACH LEVEL.
> Like this:
>
> SMITH
> JACKSON
> KELLY
> KING
> SIMON
> FORRESTER
> TACKER
> TIGER
>
> How can I do that?
>
>
>
Received on Thu Jun 07 2001 - 07:02:46 CDT