2 Replies Latest reply on Nov 18, 2012 3:46 PM by ranit B

    doubt with connect by

    9876564
      HI,

      I have a basic doubt

      a query
      SELECT employee_id, manager_id
      FROM employees
      CONNECT BY PRIOR employee_id = manager_id 
      it will give result in the below format
       
      Emp     manager
      1         
      2           1
      3           1
      4           2
      my question is even if i write a simple order by manager clause ,it willl give me the same result
      SELECT employee_id, manager_id
      FROM employees
      order by manage_id 
      can anyone tell any difference in the output.

      Abhishek
        • 1. Re: doubt with connect by
          Solomon Yakobson
          AbSHeik wrote:
          my question is even if i write a simple order by manager clause ,it willl give me the same result
          Not true:
          SQL> select  *
            2    from  employees
            3  /
          
          EMPLOYEE_ID MANAGER_ID
          ----------- ----------
                    1
                    2          1
                    3          1
                    4          2
          Now plain select returns 4 rows:
          SQL> select  employee_id,
            2          manager_id
            3    from  employees
            4    order by employee_id
            5  /
          
          EMPLOYEE_ID MANAGER_ID
          ----------- ----------
                    1
                    2          1
                    3          1
                    4          2
          
          SQL>
          While hierarchy returns 8 rows:
          SQL> select  employee_id,
            2          manager_id
            3    from  employees
            4    connect by prior employee_id = manager_id
            5  /
          
          EMPLOYEE_ID MANAGER_ID
          ----------- ----------
                    2          1
                    4          2
                    3          1
                    4          2
                    1
                    2          1
                    4          2
                    3          1
          
          8 rows selected.
          
          SQL>
          SY.
          • 2. Re: doubt with connect by
            ranit B
            Hi Abhishek,

            I guess you have misunderstood the concept of Connect By Prior.
            You can never replace the work of Hierarchical Query with an ORDER BY clause.
            It outputs the result of a Parent-Child relationship.

            Please rectify me if i'm wrong.
            I guess, the output is just a matter of by-chance which has been demonstrated later by Solomon.

            Hierarchial Queries :
            Refer -- http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries003.htm

            HTH
            Ranit B.