Re: How to control order of siblings in a tree walked by "connect by"??

In article <1993Mar17.001028.14189_at_dlogics.com>, brown_at_dlogics.com (Steve Brown) writes:
|> I cannot find any reference on how to control the ordering of rows at the
|> same level. An example is a hierarchical parts list where I want to order
|> all parts at the same level alphabetically. The "connect by" seems to use
|> the order of the rows as they were loaded into the table. I didn't
|> think a relational database knew/controlled the order of rows.
|>
|> A recursive c program to do this isn't difficult, but it doesn't return a
|> single, neat, ordered result set and probably would require a cursor per level.
|>
|> I'm probably missing something. Any hints, ideas, or references greatly
|> appreciated.
|>
|>
|> Steve Brown / Datalogics, Inc. / 441 W. Huron St. / Chicago, IL 60610
|> brown_at_dlogics.com / 312-266-4380 / ...uunet!dlogics!brown

You need to create an index on the column by which you want to sort and then force the column to use that index; e.g.; the following will sort a connect by statement based on the scott.emp table

SELECT LEVEL,ENAME,EMPNO,JOB,DEPTNO,MGR
 from emp
 CONNECT BY PRIOR EMPNO =MGR
 AND ENAME >'A'
 START WITH ENAME ='KING'
 /

if an index is placed on the ename field.

                     Ian MacGregor
                     Stanford Linear Accelerator Center
                     (415) 926-3528