CODEWITHSUNDEEP
sqlpostgresqlrecursion
robdog
robdog

Reputation: 486

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 order, and Postgres won't let me insert an ORDER BY clause before the UNION ALL.

Thanks,

Upvotes: 8

Views: 8349

Answers (2)

rfusca
rfusca

Reputation: 7705

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;

Upvotes: 7

rownage
rownage

Reputation: 2414

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 :/

Upvotes: 3

Related Questions