How to order SIBLINGS in Oracle hierarchy queries

by Bob Watkins | Dec 19, 2006 8:00:00 AM

Tags: Bob Watkins, Oracle Corp., hierarchy, clause, ORDER SIBLINGS, Oracle Tips Newsletter

Takeaway: Developers can use the ORDER SIBLINGS BY feature of Oracle 10g and later to sort the records in a hierarchy created by the START WITH and CONNECT BY clauses in a SELECT statement.

Oracle's START WITH and CONNECT BY clauses in the SELECT statement automatically traverse a hierarchy. Without this feature, a complex self-join would be required to identify which rows are logically related to others. The START WITH clause identifies the row or rows to be considered the starting points, or "roots," of the hierarchy. The CONNECT BY PRIOR clause then indicates how to identify which rows are related to each other.

For example, the query in Listing A produces a "Reports To" listing from the EMPLOYEES table in the HR sample schema provided by Oracle.

The LEVEL pseudocolumn indicates how deeply the report is currently nested; here, I use it to LPAD the employee names to indent them. The START WITH condition states that only employees 101 and 102 are to be considered as starting points. The CONNECT BY PRIOR clause then links the employee_id column in one row to the manager_id column in the next, to indicate who reports to whom.

If you run this query in the HR schema, you'll notice that the last names are not sorted within the listing for a specific manager; they are listed in the order Oracle encountered them in processing the hierarchy.

If you want the subordinates in alphabetical order, you might try to ORDER BY the original last_name column. However, this would break up the hierarchy, and turn it back into a flat list of names.

You might also try to ORDER BY the pseudocolumn LEVEL first, which tells how deep a specific row is in the hierarchy. This, too, breaks up the hierarchy—all the managers will be listed first, followed by people who report to any of them.

In Oracle 10g (both releases), it's now easy to do this: You can use the new SIBLINGS keyword to create the correct ordering. The syntax is:

ORDER SIBLINGS BY <expression>

So adding the clause:

ORDER SIBLINGS BY last_name

to the end of the query will preserve the hierarchy and also alphabetize the last names within each level. Note that the original last_name was used not the alias "Reports To." The extra space padding in "Reports To" would affect the sort, so the original must be used. Listing B shows the output, both before and after adding ORDER SIBLINGS BY.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.