HOWTO ORDER HIERARCHICAL QUERIES ?

Hello,
i have an hierarchical query which I like to have sorted on each level. Take an example from the oracle doc:

SELECT LPAD(' ',2*(LEVEL-1)) || ename org_chart,

        empno, mgr, job
    FROM emp
    START WITH job = 'PRESIDENT'
    CONNECT BY PRIOR empno = mgr;

ORG_CHART EMPNO MGR JOB
------------ ---------- ---------- ---------

KING               7839            PRESIDENT
  JONES            7566       7839 MANAGER
    SCOTT          7788       7566 ANALYST
      ADAMS        7876       7788 CLERK
    FORD           7902       7566 ANALYST
      SMITH        7369       7902 CLERK
  BLAKE            7698       7839 MANAGER
    ALLEN          7499       7698 SALESMAN
    WARD           7521       7698 SALESMAN
    MARTIN         7654       7698 SALESMAN
    TURNER         7844       7698 SALESMAN
      JAMES        7900       7698 CLERK
  CLARK            7782       7839 MANAGER
    MILLER         7934       7782 CLERK

What I need is an "order by" that gives me the result ordered by ORG_CHART on every level:

ORG_CHART EMPNO MGR JOB
------------ ---------- ---------- ---------

KING               7839            PRESIDENT
  BLAKE            7698       7839 MANAGER
    ALLEN          7499       7698 SALESMAN
    MARTIN         7654       7698 SALESMAN
    TURNER         7844       7698 SALESMAN
      JAMES        7900       7698 CLERK
    WARD           7521       7698 SALESMAN
  CLARK            7782       7839 MANAGER
    MILLER         7934       7782 CLERK
  JONES            7566       7839 MANAGER
    FORD           7902       7566 ANALYST
      SMITH        7369       7902 CLERK
    SCOTT          7788       7566 ANALYST
      ADAMS        7876       7788 CLERK

Got the point ?
I think this requirement isn't unusual. Does anybody know a solution ?

Thanks in advance.
Kai
--
Kai Poitschke MailTo:kai.poitschke[at]computer.org Received on Mon Sep 13 1999 - 05:02:15 CDT