title: Removing duplicate subtrees from CONNECT-BY query in oracle
body: <p>I have a heirarchical table in the format </p>
  
  <pre><code>CREATE TABLE tree_hierarchy (
    id        NUMBER (20)
   ,parent_id NUMBER (20)
  );
  
  
  INSERT INTO tree_hierarchy (id, parent_id) VALUES (2, 1);
  INSERT INTO tree_hierarchy (id, parent_id) VALUES (4, 2);
  INSERT INTO tree_hierarchy (id, parent_id) VALUES (9, 4);
  </code></pre>
  
  <p>When I run the Query:-</p>
  
  <pre><code>SELECT id,parent_id,
    CONNECT_BY_ISLEAF leaf,
    LEVEL,
    SYS_CONNECT_BY_PATH(id, '/') Path,
    SYS_CONNECT_BY_PATH(parent_id, '/') Parent_Path
  FROM tree_hierarchy
  WHERE CONNECT_BY_ISLEAF&lt;&gt;0
    CONNECT BY PRIOR id = PARENT_id
  ORDER SIBLINGS BY ID;
  </code></pre>
  
  <p>Result I am Getting is like this:-</p>
  
  <pre><code>"ID"    "PARENT_ID" "LEAF"  "LEVEL" "PATH"  "PARENT_PATH"
  
  9            4         1       3    "/2/4/9"  "/1/2/4"
  
  9            4         1       2     "/4/9"     "/2/4"
  
  9            4         1       1      "/9"      "/4"
  </code></pre>
  
  <p>But I need an Oracle Sql Query That gets me only this </p>
  
  <pre><code>"ID"    "PARENT_ID" "LEAF"  "LEVEL" "PATH"  "PARENT_PATH"
  
  9            4         1       3    "/2/4/9"  "/1/2/4"
  </code></pre>
  
  <p>This is a simpler example I have more that 1000 records in such fashion.When I run the above query,It is generating many duplicates.Can any one give me a generic query that will give complete path from leaf to root with out duplicates.Thanks for the help in advance</p>