Ordering a WITH RECURSIVE query in Postgres

I'm executing a recursive query in Postgres to retrieve a list of emails and their threaded children as follows:

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;

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 ord开发者_运维技巧er, and Postgres won't let me insert an ORDER BY clause before the UNION ALL.

Thanks,

This is untested, but usually i can add any ORDER BY before a union so long as there are parentheses...

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
  ORDER BY title
)
  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;

Create a view consisting of the first part of your query, ordered by title. Maybe something like this?

      CREATE VIEW title_Sort AS
      SELECT  id,  
      title, 
      array[id] AS path, 
      parent_id,  
      1 AS depth  
      FROM    emails 
      WHERE   parent_id IS NULL 
      ORDER BY title;

Then UNION ALL that view with your other query as you did before. I think that will work. On my netbook right now so I can't test :/