Hierarchical query in oracle using CONNECT BY PRIOR

Select data with a hierarchical (parent/child) relationship in oracle.

The query below can be used to retrieve records in any order of hierarchy.

Syntax:

   SELECT…  [START WITH initial_condition]
   CONNECT BY [nocycle] PRIOR recurse_condition [ORDER SIBLINGS BY order_by_clause]


EXPLANATION:

    CONNECT BY        : Condition that identifies the relationship between
                        parent and child rows of the hierarchy

    START WITH        : The row(s) to be used as the root of the hierarchy

  
    NOCYCLE           : Do not circle around loops (where the current row has a child which is also its ancestor.)

    ORDER SIBLINGS BY : Preserve ordering of the hierarchicalquery then apply the order_by_clause to the sibling rows


EXAMPLE :

Consider the following Table Structure

-- Create table

create table TABLEA
(
  id                        NUMBER not null,
  name                      VARCHAR2(200),
  occupation                VARCHAR2(100),
  dob                       DATE,
  lastid                  NUMBER default 0,

)
Sample Query:

SELECT id,name,occupation,to_date(ip.dob,'DD-MM-YYYY') DOB,lastid
FROM TABLEA start with id=690180
CONNECT BY PRIOR id=.lastid
union
SELECT
id,name,occupation,to_date(ip.dob,'DD-MM-YYYY') DOB,lastid
FROM TABLEA start with id=690180
CONNECT BY PRIOR lastid=id;