ORDER SIBLINGS BY Clause

The ORDER SIBLINGS BY clause is valid only in a hierarchical query. The optional SIBLINGS keyword specifies an order that first sorts the parent rows, and then sorts the child rows of each parent for every level within the hierarchy.

Rows that have duplicate lists of values in the columns specified after the SIBLINGS BY keywords are arbitrarily ordered among the rows with the same list of values and the same parent. If a hierarchical query includes the ORDER BY clause without the SIBLINGS keyword, rows are ordered according to the sort specifications that follow the ORDER BY keywords. Neither the ORDER BY clause nor the ORDER SIBLINGS BY option to the ORDER BY clause is required in hierarchical queries.

The hierarchical query in the following example returns the subset of rows in the hierarchical data set whose root is Goyal, as listed in the topic Hierarchical Clause. This query includes the ORDER SIBLINGS BY clause to sort by name the employees who report to the same manager:

SELECT empid, name, mgrid, LEVEL
   FROM employee
      START WITH name = 'Goyal'
      CONNECT BY PRIOR empid = mgrid
   ORDER SIBLINGS BY name;

The rows returned by this query are sorted in the following order:

         empid name             mgrid       level 

         16 Goyal               17           1
         12 Henry               16           2
          7 O'Neil              12           3
          9 Shoeman             12           3
          8 Smith               12           3
         14 Scott               16           2
         11 Zander              16           2
          6 Barnes              11           3
          5 McKeough            11           3

9 row(s) retrieved.

Here the START WITH clause returned the Goyal row at the root of this hierarchy. Two subsequent CONNECT BY steps (marked as 2 and 3 in the level pseudocolumn) returned three sets of sibling rows:

Henry, Scott, and Zander are siblings whose parent is Goyal;
O'Neil, Shoeman, and Smith are siblings whose parent is Henry;
Barnes and McKeough are siblings whose parent is Zander.

The next CONNECT BY step returned no rows, because the rows for which level = 3 are leaf nodes within this hierarchy. At this point in the execution of the query, the ORDER SIBLINGS BY clause was applied to the result set, sorting the rows in the order shown above.

Because the sort key, name, is a VARCHAR column, the returned rows within each set of siblings are in the ASCII order of their employee.name values. Only the sets of siblings that are leaf nodes in the hierarchy of returned rows appear consecutively in the sorted result set, because the managers are immediately followed by the employees who report to them, rather than by their siblings. An exception in this example is Scott, whose child nodes form an empty set.

The SIBLINGS keyword in the ORDER BY clause is an extension to the ISO standard syntax for the SQL language. The SELECT statement fails with an error if you include the SIBLINGS keyword in the ORDER BY clause of a query or subquery that does not include a valid CONNECT BY clause.

For more information about hierarchical queries and the CONNECT BY clause, see Hierarchical Clause.