This is tricky since the Order by happens AFTER the CONNECT BY and hence will destroy the result sets 'hierarchy'. You typically never use connect by AND order by.
One solution that always works (in Oracle8i and up) is demonstrated at </code>
http://asktom.oracle.com/~tkyte/autonomous/index.html <code>in the section on "Writing to the database state in a function called from SQL". It does not use a connect by but achieves the same hiearchy and is guaranteed to be ordered.
Another method by should work in most cases (you'll have to verify it works on your data, with your query and if you 'ad-hoc' the query by adding predicates dynamically at run time, you'll need to be very careful with this solution) is to use an index to help the sort order. We'll also use an index HINT in the event the columns we are connecting by are indexed in other ways as well.
To demonstrate this technique -- we will use the scott/tiger table EMP.
ops$tkyte@8i> create table emp as select * from scott.emp;
ops$tkyte@8i> alter table emp add constraint emp_pk primary key(empno);
ops$tkyte@8i> create index emp_idx1 on emp(mgr,job);
So, we have the emp table with 2 indexes now, one on empno and one on (mgr,job).
The connect by query we will run will use "connect by prior empno = mgr". That means for each row we start with -- the database will effectively do "select * from emp where mgr = :PRIOR_EMPNO" to find the subordinate records. It will be looking for an index that has the MGR column on the leading edge. Since we have one on (mgr,job), and the database finds that index -- the data will come out in a hirearchy ordered by JOB in each level. We can see this with:
ops$tkyte@8i> set autotrace on explain
ops$tkyte@8i> select lpad( '*', level*2 ) || ename ename,
2 job
3 from emp
4 start with mgr is null
5 connect by prior empno = mgr
6 /
ENAME JOB
-------------------- ---------
*KING PRESIDENT
*JONES MANAGER
*SCOTT ANALYST
*ADAMS CLERK
*FORD ANALYST
*SMITH CLERK
*BLAKE MANAGER
*JAMES CLERK
*ALLEN SALESMAN
*WARD SALESMAN
*MARTIN SALESMAN
*TURNER SALESMAN
*CLARK MANAGER
*MILLER CLERK
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 CONNECT BY
2 1 TABLE ACCESS (FULL) OF 'EMP'
3 1 TABLE ACCESS (BY USER ROWID) OF 'EMP'
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
5 4 INDEX (RANGE SCAN) OF 'EMP_IDX1' (NON-UNIQUE)
See how the optimizer found and used the EMP_IDX1 to retrieve the data and the data is in fact sorted by JOB at each level. Suppose we wanted to be sorted by ENAME at each level. We should create an index then on MGR,ENAME and use a hint to tell Oracle we want to use that index in the connect by. It would look like this:
ops$tkyte@8i> create index emp_connect_by_idx on emp(mgr,ename)
2 /
ops$tkyte@8i> select /*+ INDEX(emp emp_connect_by_idx) */
2 lpad( '*', level*2 ) || ename ename,
3 job
4 from emp
5 start with mgr is null
6 connect by prior empno = mgr
7 /
ENAME JOB
-------------------- ---------
*KING PRESIDENT
*BLAKE MANAGER
*ALLEN SALESMAN
*JAMES CLERK
*MARTIN SALESMAN
*TURNER SALESMAN
*WARD SALESMAN
*CLARK MANAGER
*MILLER CLERK
*JONES MANAGER
*FORD ANALYST
*SMITH CLERK
*SCOTT ANALYST
*ADAMS CLERK
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=39)
1 0 CONNECT BY
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=5 Bytes=65)
3 1 TABLE ACCESS (BY USER ROWID) OF 'EMP'
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=39)
5 4 INDEX (RANGE SCAN) OF 'EMP_CONNECT_BY_IDX' (NON-UNIQUE) (Cost=2 Card=1)
We verified the query plan is using our index and in fact the data is sorted now by ENAME at each level, not by job. If you need 2 different sorts -- you can still do that, we can get our original "sort by job" by hinting the query to use that index:
ops$tkyte@8i> select /*+ INDEX(emp emp_idx1) */
2 lpad( '*', level*2 ) || ename ename,
3 job
4 from emp
5 start with mgr is null
6 connect by prior empno = mgr
7 /
ENAME JOB
-------------------- ---------
*KING PRESIDENT
*JONES MANAGER
*SCOTT ANALYST
*ADAMS CLERK
*FORD ANALYST
*SMITH CLERK
*BLAKE MANAGER
*JAMES CLERK
*ALLEN SALESMAN
*WARD SALESMAN
*MARTIN SALESMAN
*TURNER SALESMAN
*CLARK MANAGER
*MILLER CLERK
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=39)
1 0 CONNECT BY
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=5 Bytes=65)
3 1 TABLE ACCESS (BY USER ROWID) OF 'EMP'
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=39)
5 4 INDEX (RANGE SCAN) OF 'EMP_IDX1' (NON-UNIQUE) (Cost=2 Card=1)
So, it is sorted by JOB once again.
You should consider using QUERY Plan stability with Oracle8i, release 8.1 once you get the right plan in place. This will make it such that once you get the plan that uses the index you want -- it will be stored in the database and you can request at run time to use "plan X" or "plan Y" for a given query. This will protect you from changes in the plan over time as different statistics are collected, versions are upgraded and so on. You must be careful to have the correct indexes in place, else the data will *not* be ordered and the fact the data is not ordered is not considered an error (you'll not be notified that the index you want is not in place)