CONNECT BY PRIOR and ORDER SIBLINGS BY

Tested on an Oracle 9 database. Connect by prior was available in Oracle 8. It allows you to show recursive relationships such as managers and their employees or assemblies and their sub assemblies in a top-down fashion. The order by siblings clause is new. It lets you sort the siblings at each level. You can see the difference in the example below where the same data is displayed twice. The first time the siblings are not sorted whereas the second time they are:
  

SQL> col Manager format a15

SQL> col Employee format a15

SQL> create table emp

  2  (ename varchar2(10),

  3   mgr   varchar2(10));

 

Table created.

 

SQL> insert into emp values('Russell','King');

 

1 row created.

 

SQL> insert into emp values('Smith','King');

 

1 row created.

 

SQL> insert into emp values('Jones','King');

 

1 row created.

 

SQL> insert into emp values('Morrison','Russell');

 

1 row created.

 

SQL> insert into emp values('Matthews','Russell');

 

1 row created.

 

SQL> insert into emp values('Pearson','Russell');

 

1 row created.

 

SQL> insert into emp values('Williams','Smith');

 

1 row created.

 

SQL> insert into emp values('Brown','Smith');

 

1 row created.

 

SQL> insert into emp values('Fielder','Brown');

 

1 row created.

 

SQL> insert into emp values('Moir','Fielder');

 

1 row created.

 

SQL> select lpad(' ',level-1)||mgr Manager,

  2  lpad(' ',level-1)||ename Employee

  3  from emp

  4  start with mgr = 'King'

  5  connect by prior ename = mgr

  6  /

 

MANAGER         EMPLOYEE

--------------- ---------------

King            Russell

 Russell         Morrison

 Russell         Matthews

 Russell         Pearson

King            Smith

 Smith           Williams

 Smith           Brown

  Brown           Fielder

   Fielder         Moir

King            Jones

 

10 rows selected.

 

SQL> select lpad(' ',level-1)||mgr Manager,

  2  lpad(' ',level-1)||ename Employee

  3  from emp

  4  start with mgr = 'King'

  5  connect by prior ename = mgr

  6  order siblings by ename

  7  /

 

MANAGER         EMPLOYEE

--------------- ---------------

King            Jones

King            Russell

 Russell         Matthews

 Russell         Morrison

 Russell         Pearson

King            Smith

 Smith           Brown

  Brown           Fielder

   Fielder         Moir

 Smith           Williams

 

10 rows selected.

 

SQL>