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

"Jürgen Kärner" wrote:
>
> 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?

• No hint SELECT LPAD(' ', (LEVEL-1)*3) || LEVEL || ' ' || empno || '. ' || ename || ' (' || mgr || ')' FROM emp CONNECT BY PRIOR empno = mgr START WITH mgr IS NULL ;

CREATE INDEX emp#i#mgr#ename

   ON emp(mgr, ename)
;

• With INDEX hint SELECT /*+ INDEX(emp emp#i#mgr#ename) */ LPAD(' ', (LEVEL-1)*3) || LEVEL || ' ' || empno || '. ' || ename || ' (' || mgr || ')' FROM emp CONNECT BY PRIOR empno = mgr START WITH mgr IS NULL ;

Valery Yourinsky

-- 
Oracle8 Certified DBA
Moscow, Russia