title: PostgreSQL recursive with
body: <p>I need help with a recursive query. Assuming the following table:</p>
  
  <pre><code>CREATE TEMPORARY TABLE tree (
      id        integer PRIMARY KEY,
      parent_id integer NOT NULL,
      name      varchar(50)
  );    
  
  INSERT INTO tree (id, parent_id, name) VALUES (3, 0, 'Peter'), (2,0, 'Thomas'), (5,2, 'David'), (1, 0, 'Rob'), (8, 0, 'Brian');
  </code></pre>
  
  <p>I can retrieve a list of all people and their children with the following query:</p>
  
  <pre><code>WITH RECURSIVE recursetree(id, parent_id) AS (
      SELECT id, parent_id FROM tree WHERE parent_id = 0
    UNION
      SELECT t.id, t.parent_id
      FROM tree t
      JOIN recursetree rt ON rt.id = t.parent_id
    )
  SELECT * FROM recursetree;
  </code></pre>
  
  <p>How can I list them in order, and also sort the first level items by name? For example, the desired output would be:</p>
  
  <pre><code>id, parent_id, name    
  8, 0, "Brian"
  3, 0, "Peter"
  1, 0; "Rob"
  2, 0, "Thomas"
  5, 2, "  David"
  </code></pre>
  
  <p>Thanks,</p>
  
  <p>**EDIT. Please note that adding an ORDER BY won't work: **</p>
  
  <pre><code>WITH RECURSIVE recursetree(id, parent_id, path, name) AS (
      SELECT 
          id, 
          parent_id, 
          array[id] AS path, 
          name 
      FROM tree WHERE parent_id = 0
    UNION ALL
      SELECT t.id, t.parent_id, rt.path || t.id, t.name
      FROM tree t
      JOIN recursetree rt ON rt.id = t.parent_id
    )
  SELECT * FROM recursetree ORDER BY path;
  </code></pre>
  
  <p>The above will retain the parent child relationship (children follow their parents), but applying any other ORDER BY clause (ie: name - like some have suggested) will cause the result to lose it's parent-child relationships.</p>