title: Oracle CONNECT BY recursive child to parent query, include ultimate parent that self references
body: <p>In the following example</p>
  
  <pre><code>id parent_id
  A  A
  B  A
  C  B
  
  select id, parent_id
  from table
  start with id = 'A'
  connect by nocycle parent_id = prior id
  </code></pre>
  
  <p>I get</p>
  
  <pre><code>A A
  B A
  C B
  </code></pre>
  
  <p>In my database I have millions of rows in the table and deep and wide hierarchies and I'm not interested in all children. I can derive the children I'm interested in. So I want to turn the query on its head and supply START WITH with the children ids. I then want to output the parent recursively until I get to the top. In my case the top is where the id and parent_id are equal. This is what I'm trying, but I can't get it to show the top level parent.</p>
  
  <pre><code>select id, parent_id
  from table
  START WITH id = 'C'
  CONNECT BY nocycle id = PRIOR parent_id
  </code></pre>
  
  <p>This gives me</p>
  
  <pre><code>C B
  B A
  </code></pre>
  
  <p>It's not outputting the A A. Is it possible to do this? What I'm hoping to do is not show the parent_id as a separate column in the output, but just show the name relating to the id. The hierarchy is then implied by the order.</p>