A Simple Technique for Sorting Pre-Oracle10g Hierarchical Query Result Sets
Several week's ago a student asked how to sort the rows in a Hierarchical Query result set. Oracle's Hierarchical Query analyzes a result set and places the results in a hierarchy with the children placed under the proper parent. However, Oracle does not place children rows in any type of sorted order. The student liked the idea of placing a child under the proper parent, but wanted to sort the child rows alphabetically within the level. This is was a bit of a challenge since the use of the Order By clause destroys the hierarchy. In Oracle10g, Oracle has given us an option that enables the developer to sort rows without destroying the hierarchy (see "Oracle's Hierarchical Query and 10g Enhancements", August 20, 2004) . However, this option does not exist in Pre-Oracle10g databases. This article offers an easy solution to properly sort hierarchical rows in Pre-Oracle 10g databases.
Listing 1 illustrates this sorting problem. The listing depicts a common query used when discussing hierarchical queries. The Select statement produces a hierarchical result set from Oracle's sample EMP table. Notice that the second level employees, Jones, Blake, and Clark are in a random order. In addition third level employees, Allen, Ward, Martin, Turner, and James are also in random order. The order of the displayed values is determined by the record position in the result set. Since the result set is unordered, the displayed records are unordered.
Listing 1 - Unsorted Hierarchical Query using a table as a data source
SQL> select lpad(' ', 2*(level-1))||level lvl,
2 lpad(' ', 2*(level-1))||ename ename, job
3 from emp
4 start with ename = 'KING'
5 connect by prior empno = mgr;
LVL ENAME JOB
---------- ------------------------------ ---------
1 KING PRESIDENT
2 JONES MANAGER
3 SCOTT ANALYST
4 ADAMS CLERK
3 FORD ANALYST
4 SMITH CLERK
2 BLAKE MANAGER
3 ALLEN SALESMAN
3 WARD SALESMAN
3 MARTIN SALESMAN
3 TURNER SALESMAN
3 JAMES CLERK
2 CLARK MANAGER
3 MILLER CLERK
14 rows selected.
SQL>
The sorting problem is solved by sorting the rows prior to Oracle evaluating the result set for the hierarchy. This is done in Listing 2. The From clause Emp table reference is replaced with an in-line view that selects all Emp table records and orders the rows by Ename. Oracle still displays the hierarchy rows as they are identified, but the displayed rows are sorted since the source data set is sorted. This is evident in Listing 2. Notice that the second level employees are now sorted alphabetically (Blake, Clark, and Jones). The is also true of the third level employees (Allen, James, Martin, Turner, and Ward).
Listing 2 - Sorted Hierarchical Query result set using an in-line view as a data source
SQL> select lpad(' ', 2*(level-1))||level lvl,
2 lpad(' ', 2*(level-1))||ename ename, job
3 from (select *
4 from emp
5 order by ename)
6 start with ename = 'KING'
7* connect by prior empno = mgr
LVL ENAME JOB
---------- ------------------------------ ---------
1 KING PRESIDENT
2 BLAKE MANAGER
3 ALLEN SALESMAN
3 JAMES CLERK
3 MARTIN SALESMAN
3 TURNER SALESMAN
3 WARD SALESMAN
2 CLARK MANAGER
3 MILLER CLERK
2 JONES MANAGER
3 FORD ANALYST
4 SMITH CLERK
3 SCOTT ANALYST
4 ADAMS CLERK
14 rows selected.
SQL>
The technique is very simple and easy to use. However, you cannot use the in-line view if it contains a Distinct keyword, Group by clause, or a join. The in-line view data source must be a single table. If you need to join the Hierarchical Query records to another data source, remember that you can place the entire clause into another in-line view and join the result set to the other data source.
John Adolph Palinski is a former adjunct faculty member at the University of Nebraska at Omaha and Iowa Western Community College. He is the author of the "Oracle Database Construction Kit", Que, 1997, the "Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts", Addison Wesley Longman, 2002, and "Oracle 9i Developer: Developing Web Applications With Forms Builder", Course Technology, 2003. He has also written numerous articles for periodicals such as Oracle Internals and Oracle Professional. He provides Oracle training and consulting services. One of his passions is to help customers set up logical business models to improve information efficiency and effectiveness. He may be contacted at www.oracle-trainer.com.
