Ordering a WITH RECURSIVE query in Postgres

Question

I do a recursive query in Postgres to get a list of letters and their children with a conversation:

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 the ordering of the list change (for example, sorting by title) before finding emails for children. I obviously need to save the external ORDER BY so that the list is restored in its tree order, and Postgres will not let me insert an ORDER BY clause before UNION ALL.

Thank,

Answer 1

Create a view consisting of the first part of your query, ordered by name. 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;

UNION ALL, , . , . , :/

Answer 2

, ORDER BY , ...

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;