Reverse SELECT query result

Question

I have recursive query which returns some rows from hierarchical model. Kind Of:

files_array := ARRAY
   (WITH RECURSIVE files_to_parent AS (
          SELECT FileID, Name, ParentID
          FROM File
          WHERE FileID = file_id
       UNION ALL
          SELECT F.FileID, F.Name, F.ParentID
          FROM files_to_parent ftp, File F
          WHERE F.FileID = FTP.ParentID
    )
    SELECT Name FROM files_to_parent);

How can I reverse the result of the SELECT query?

PS: I cannot order by IDs, id of parent can be more or less then in child.

Add an artificial "recursion depth" column and sort by that. — Laurenz Albe, Jan 31 '19 at 12:04
@LaurenzAlbe Great, thanks. I knew that the answer is on the top ))) — Serhiy, Jan 31 '19 at 12:06

score 1 · Accepted Answer · answered Jan 31 '19 at 12:47

You have to add an explicit ORDER BY clause to get a certain order:

WITH RECURSIVE files_to_parent AS (
      SELECT FileID, Name, ParentID,
             1 AS recursion_depth
      FROM File
      WHERE FileID = file_id
   UNION ALL
      SELECT F.FileID, F.Name, F.ParentID,
             ftp.recursion_depth + 1
      FROM files_to_parent ftp
         JOIN File F ON F.FileID = FTP.ParentID
)
SELECT Name
FROM files_to_parent
ORDER BY recursion_depth DESC;

Reverse SELECT query result

1 Answers1