In a 'start with / connect by' query, how do I get child rows under each parent to come out in sorted order ?

The Oracle (tm) Users' Co-Operative FAQ

In a 'start with / connect by' query, how do I get child rows under each parent to come out in sorted order ?

There are three techniques that can be used for sorting the child rows in a connect by query. These are fairly well documented in MetaLink articles (see references below). Each has advantages and disadvantages.

The first technique is to create a recursive function that will calculate the correct order for a given row and then order by that function. The second technique is a variation on the first that saves the value in a new column in the table, then orders by the new column. The advantage of these two techniques is that they are guaranteed to work even if the execution plan of the query changes. The disadvantage is that they both require a stored function and the second requires changes to the table schema.

The third technique is to create an index and modify the query so that the optimizer uses the index, resulting in the child rows coming out in sorted order. The advantage of this technique is that it is fairly simple to implement, runs quickly and doesn't require any changes to the table. The disadvantage is that it relies on the execution plan of the query which can change if indexes or other structures change.

The code samples below were copied directly from the MetaLink articles with a few changes.

Here is the basic query, not sorted:

SELECT LPAD(' ' ,2*level) || ename employee
  FROM emp
 START WITH ename ='KING'
       CONNECT BY PRIOR empno= mgr
/
EMPLOYEE
------------------------------
  KING
    JONES
      SCOTT
        ADAMS
      FORD
        SMITH
    BLAKE
      ALLEN
      WARD
      MARTIN
      TURNER
      JAMES
    CLARK
      MILLER

Here is a simple recursive function that computes the sort order (lineage) for a given employee.

CREATE OR REPLACE FUNCTION lineage(v_empno number)
RETURN VARCHAR2
IS
   v_mgr    NUMBER(10);
   v_ename  VARCHAR2(30);
BEGIN
   SELECT mgr, ename
     INTO v_mgr, v_ename
     FROM emp
    WHERE empno=v_empno;
   IF v_mgr IS NULL THEN
      RETURN(v_ename);
   ELSE
      RETURN(lineage(v_mgr) ||'-' || v_ename);
   END if;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RETURN(NULL);
END;
/

Once you've created the function, you can modify the query to use it for the sort order:

SELECT LPAD(' ' ,2*level) || ename employee,
       lineage(empno) lineage
  FROM emp
 START WITH ename ='KING'
       CONNECT BY PRIOR empno= mgr
 ORDER BY 2
/
EMPLOYEE                       LINEAGE
------------------------------ ------------------------------
  KING                         KING
    BLAKE                      KING-BLAKE
      ALLEN                    KING-BLAKE-ALLEN
      JAMES                    KING-BLAKE-JAMES
      MARTIN                   KING-BLAKE-MARTIN
      TURNER                   KING-BLAKE-TURNER
      WARD                     KING-BLAKE-WARD
    CLARK                      KING-CLARK
      MILLER                   KING-CLARK-MILLER
    JONES                      KING-JONES
      FORD                     KING-JONES-FORD
        SMITH                  KING-JONES-FORD-SMITH
      SCOTT                    KING-JONES-SCOTT
        ADAMS                  KING-JONES-SCOTT-ADAMS

If you have a large table, it may make sense to store the sort order in a column with the table so that your query will run faster. I've used a very simple technique for creating the new column:

CREATE TABLE tmp_emp AS
SELECT empno, lineage(empno) lineage
  FROM emp
/
ALTER TABLE emp ADD lineage VARCHAR2(4000);
ALTER FUNCTION lineage COMPILE;
UPDATE emp e
   SET lineage =
       (SELECT lineage
          FROM tmp_emp t
         WHERE t.empno = e.empno)
/
COMMIT;
SELECT LPAD(' ', 2*(level)) || ename employee
  FROM emp
 START WITH ename = 'KING'
       CONNECT BY prior empno = mgr
 ORDER BY lineage
/
EMPLOYEE
------------------------------
  KING
    BLAKE
      ALLEN
      JAMES
      MARTIN
      TURNER
      WARD
    CLARK
      MILLER
    JONES
      FORD
        SMITH
      SCOTT
        ADAMS

You should also note that you don't really need the connect by to get the correct sort order unless you need the level pseudo-column.

SELECT ename employee
  FROM emp
 ORDER BY lineage
/
EMPLOYEE
------------------------------
KING
BLAKE
ALLEN
JAMES
MARTIN
TURNER
WARD
CLARK
MILLER
JONES
FORD
SMITH
SCOTT
ADAMS

The final technique, and my preferred one, is to create an index on the column that you want to sort by and then persuade the optimizer to use it:

CREATE INDEX org_chart_idx ON emp(ename);
SELECT LPAD(' ', 2*(level)) || ename employee
  FROM emp
 START WITH ename = 'KING'
       CONNECT BY PRIOR empno = mgr
           AND ename =' '  -- This predicate should make the optimizer use the index.
/
EMPLOYEE
------------------------------
  KING
    BLAKE
      ALLEN
      JAMES
      MARTIN
      TURNER
      WARD
    CLARK
      MILLER
    JONES
      FORD
        SMITH
      SCOTT
        ADAMS

Further reading: Metalink articles:

73830.1 How to sort records in connect by select statement w/o destroying tree structure

1009345.6 Want to order rows in a hierarchical query