How to Order Siblings in Hierarchical Queries

In a hierarchical query, if you specify either ORDER BY or GROUP BY, the hierarchical order will be destroyed. If we need to sort the rows of siblings of the the same level (while keeping the hierarchical order) we can to use ORDER SIBLINGS BY clause. Let’s create a sample table:

Let’s list these rows in a hierarchical order:

If we want to order the employees at the same level according to their names, we can use siblings keyword:

Please share
  •  
  •  
  •  
  •  
  •  
  •  

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

5 Comments

  1. Ajesh Rajan

    Thanks for your explanation.

    I have requirement to order data at third level using other Column e.g. in this case Brown and Lee has to be ordered using third column. Whether it is possible?

     

    • Gokhan Atil

      Ajesh,

      Yes it’s possible. You can order them using another column or expression. The following one will order them randomly:

  2. Vamsi

    Hi

    I have a SQL query with UNION ALL operator.

    The Second Select statement in the UNION ALL has the SYS_CONNECT_BY_PATH

    START WITH

    CONNECT BY PRIOR.

    In this case I would like to sorting to be based on the second SELECT statement in the UNION ALL.

    So I used the second select statement within in parenthesis as shown below:

    SELECT NULL alias1, NULL alias2 FROM table1, table2 WHERE col1.table1 = col2.table2 UNION ALL (SELECT col1, col2 FROM table1, table2 WHERE col1.table1 = col2.table2 START WITH <some col> CONNECT BY NOCYCLE PRIOR <some col> = <some col>) ORDER SIBLINGS BY col1, col2

    I tried to use ORDER SIBLINGS BY but the error shows up that ORDER SIBLINGS BY is not allowed here

    Please advice.

     

     

Leave a Reply to Vamsi Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.