StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow do I remember which way round PRIOR should go in CONNECT BY queries
text
Body
copied!<p>I've a terrible memory. Whenever I do a CONNECT BY query in Oracle - and I do mean <em>every</em> time - I have to think hard and usually through trial and error work out on which argument the PRIOR should go.</p> <p>I don't know why I don't remember - but I don't.</p> <p>Does anyone have a handy memory mnemonic so I always remember ?</p> <p>For example:</p> <p>To go <strong>down</strong> a tree from a node - obviously I had to look this up :) - you do something like:</p> <pre><code>select * from node connect by prior node_id = parent_node_id start with node_id = 1 </code></pre> <p>So - I start with a <code>node_id</code> of 1 (the top of the branch) and the query looks for all nodes where the <code>parent_node_id</code> = 1 and then iterates down to the bottom of the tree.</p> <p>To go <strong>up</strong> the tree the prior goes on the parent:</p> <pre><code>select * from node connect by node_id = prior parent_node_id start with node_id = 10 </code></pre> <p>So starting somewhere down a branch (<code>node_id = 10</code> in this case) Oracle first gets all nodes where the <code>parent_node_id</code> is the same as the one for which <code>node_id</code> is 10.</p> <p><strong>EDIT</strong>: I <strong>still</strong> get this wrong so thought I'd add a clarifying edit to expand on the accepted answer - here's how I remember it now:</p> <pre><code>select * from node connect by prior node_id = parent_node_id start with node_id = 1 </code></pre> <p>The 'english language' version of this SQL I now read as...</p> <blockquote> <p>In NODE, starting with the row in which <code>node_id = 1</code>, the next row selected has its <code>parent_node_id</code> equal to <code>node_id</code> from the previous (prior) row.</p> </blockquote> <p><strong>EDIT</strong>: Quassnoi makes a great point - the order you write the SQL makes things a lot easier.</p> <pre><code>select * from node start with node_id = 1 connect by parent_node_id = prior node_id </code></pre> <p>This feels a lot clearer to me - the "start with" gives the first row selected and the "connect by" gives the next row(s) - in this case the children of node_id = 1.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload