CODEWITHSUNDEEP
sqlpostgresqlrecursive-query
Matt Larsuma
Matt Larsuma

Reputation: 1519

PostgreSQL, reverse a recursive query when there isn't an ORDER BY option available

I have this recursive query that takes a child row and queries for each subsequent parent row that may be available and currently returns the result set in order of child-most to parent-most:

WITH recursive subdomains AS (
  SELECT id, parent_id, domain FROM domains WHERE id = '42b2af32-21a0-4212-b634-19ec47982e6b'
  UNION
  SELECT d.id, d.parent_id, d.domain FROM domains d 
  INNER JOIN subdomains s ON s.parent_id::text=d.id::text
) SELECT * FROM subdomains

I would like to do an exact reverse on this so it would return the results from parent-most to child-most. Would it make most sense to add an incrementing index column to then be able to ORDER BY on that?

Upvotes: 3

Views: 1395

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

It is often handy to include a "depth" column in such queries:

WITH recursive subdomains AS (
      SELECT id, parent_id, domain, 1 as lev
      FROM domains
      WHERE id = '42b2af32-21a0-4212-b634-19ec47982e6b'
      UNION ALL
      SELECT d.id, d.parent_id, d.domain, s.lev + 1
      FROM domains d INNER JOIN
           subdomains s
           ON s.parent_id::text = d.id::text  -- is type conversion really necessary?
     )
SELECT s.*
FROM subdomains s;

If I understand correctly, you can then just add ORDER BY lev DESC to the final query.

Upvotes: 7

Related Questions