title: Ordering a WITH RECURSIVE query in Postgres
body: <p>I'm executing a recursive query in Postgres to retrieve a list of emails and their threaded children as follows:</p>
  
  <pre><code>WITH RECURSIVE cte (id, title, path, parent_id, depth)  AS (
    SELECT  id, 
            title,
            array[id] AS path,
            parent_id, 
            1 AS depth
    FROM    emails
    WHERE   parent_id IS NULL
  
    UNION ALL
  
    SELECT  emails.id,
            emails.title,
            cte.path || emails.id,
            emails.parent_id, 
            cte.depth + 1 AS depth
    FROM    emails
            JOIN cte ON emails.parent_id = cte.id
  )
  SELECT id, title, path, parent_id, depth FROM cte
  ORDER BY path;
  </code></pre>
  
  <p>How would go about changing the order of the list (for example sorting on title) before finding children emails. I obviously need to keep the outer ORDER BY so that the list is retrieved in it's tree order, and Postgres won't let me insert an ORDER BY clause before the UNION ALL.</p>
  
  <p>Thanks,</p>