Re: Order hierarchy of CONNECT BY - SELECT statement

order by and connect by do not get along...

this sucks for things like you've posted, and for ordering, say, bills of material by part number...

you need to do a custom job of sorting, which is a pain.

randy

todecker_at_my-deja.com wrote:
>
> Does anybody know how to order a hierarchy of a CONNECT BY-SELECT?
> For example:
> The result of
>
> select level, empno, ename, job, mgr
> from emp
> connect by prior empno=mgr
> start with empno=7839;
>
> is
>
> LEVEL EMPNO ENAME JOB MGR
> ---------- ---------- ---------- --------- ----------
> 1 7839 KING PRESIDENT
> 2 7566 JONES MANAGER 7839
> 3 7788 SCOTT ANALYST 7566
> 4 7876 ADAMS CLERK 7788
> 3 7902 FORD ANALYST 7566
> 4 7369 SMITH CLERK 7902
> 2 7698 BLAKE MANAGER 7839
> 3 7499 ALLEN SALESMAN 7698
> 3 7521 WARD SALESMAN 7698
> 3 7654 MARTIN SALESMAN 7698
> 3 7844 TURNER SALESMAN 7698
> 3 7900 JAMES CLERK 7698
> 2 7782 CLARK MANAGER 7839
> 3 7934 MILLER CLERK 7782
>
> Now I want to keep the correct hierarchy but ordered by ename. FORD and
> its subordinate SMITH should be ordered before SCOTT and its
> subordinate ADAMS.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Nov 08 1999 - 09:39:23 CST