title: Order by in a recursive query in postgres
body: <p>I have the following query:</p>
  
  <pre><code>with recursive chain(id, weight, depth) as (
  select rpCore.itemID2, rpCore.weight, 1 as depth from relations rpCore where itemID1 = 1048663
  union 
  select r.itemID2, c.weight + r.weight, c.depth + 1from chain c
  inner join relations r
  on (r.itemID1=id)
  where c.depth &lt; 3
  order by c.weight + r.weight asc limit 10
  ) select * from chain order by weight asc limit 10;
  </code></pre>
  
  <p>If I run it, I get the following error:</p>
  
  <pre><code>ERROR: ORDER BY in a recursive query is not implemented
  SQL state: 0A000
  Character: 313
  </code></pre>
  
  <p>Because of the way the weights are structured (all positive, and added together) if I could get the recursive part of the query to order and limit itself, the outer part of the query would return the results I need, but it appears that you can't do an order by in the recursive part of a recursive query.</p>
  
  <p>Is there any way to simulate this behavior?  If I remove the limit statement, the query runs, but it runs very slowly due to the large number of (mostly unnecessary because of the final limit statement) rows.</p>