How to reverse the results of this query?

I'm using Firebird 2.1. There is a table name Folders, with these fields:

FolderID
ParentFol开发者_运维知识库derID
FolderName

ParentFolderID is -1 if it's the root folder - otherwise it contains the parent folder's ID.

The following recursive query will return the parents of a folder, in order:

WITH RECURSIVE hierarchy (folderid, ParentFolderId, FolderName) as (
   SELECT folderid, ParentFolderId, FolderName
   FROM folders
   WHERE folderid = :folderid

   UNION ALL

   SELECT folderid, ParentFolderId, FolderName
   FROM folders f
     JOIN hierarchy p ON p.parentFolderID = f.folderID
)
SELECT List(FolerName, ' \ ') FROM hierarchy

The result will be something like:

Child \ Parent \ Parent's parent

How can I reverse the results of the above query to get:

Parent's parent \ Parent \ Child?

Thank you!

---

The order of values returned by LIST is undefined.

You may try wrapping a query into a subselect:

WITH RECURSIVE
        hierarchy (folderid, ParentFolderId, FolderName, rn) AS
        (
        SELECT  folderid, ParentFolderId, FolderName, 1
        FROM    folders
        WHERE   folderid = :folderid
        UNION ALL
        SELECT  folderid, ParentFolderId, FolderName, p.rn + 1
        FROM    folders f
        JOIN    hierarchy p
        ON      p.parentFolderID = f.folderID
        )
SELECT  LIST(FolderName, ' \ ')
FROM    (
        SELECT  *
        FROM    hierarchy
        ORDER BY
                rn DESC
        )

, however, this is not guaranteed to work and even if it does by accident, it can break with any new version.

---

Add a calculated int field in the first select statement in hierarcy, and increment it. That way the result of hierarcy will have incremental filed to reverse sort on.

Note, I'm not Firebird guy, so it's just a proposal.