How to reverse the results of this query?

Question

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

FolderID
ParentFolderID
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!

According to this page: http://www.firebirdsql.org/refdocs/langrefupd21-aggrfunc-list.html , the ordering of the list values is undefined for `LIST()`. — ypercubeᵀᴹ, Jul 21 '11 at 12:46
In my experience, it always returns rows in the order the query gives them. I'm going to ask this at the FB support mailing list... — Steve, Jul 21 '11 at 12:56

Quassnoi · Accepted Answer · 2011-07-21T13:04:51.233

1

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.

edited Jul 21 '11 at 13:04

answered Jul 21 '11 at 12:51

Quassnoi

413,100
91
616
614

Hello! Why this isn't guaranteed to work? List is supposed to return results in the order that the query gives them like Row1 \ Row2 \ Row3 in this case. – Steve Jul 21 '11 at 12:54
1

@Steve: `LIST` is not supposed to return results in any certain order. If it were supposed to do something like that, that would be covered in documentation. Don't be surprised when they release a multiprocessor version which would query the tables in parallel and your assumed `LIST` order suddenly breaks. – Quassnoi Jul 21 '11 at 12:56
This is bad news. But otherwise if I compile the results to a list myself (or use a stored proc for that), the above query should work fine, right? – Steve Jul 21 '11 at 13:01
@Steve: You mean "compile on the client side"? Yes, the `ORDER BY` is guaranteed to work (if applied to the end of the query, not to any subqueries). – Quassnoi Jul 21 '11 at 13:02
Yes, either compile on the client side or write DB stored proc for compiling it "manually". Thanks! – Steve Jul 21 '11 at 13:09

Sunny Milenov · Answer 2 · 2011-07-21T12:49:25.163

0

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.

edited Jul 21 '11 at 12:49

answered Jul 21 '11 at 12:43

Sunny Milenov

21,990
6
80
106

How to reverse the results of this query?

2 Answers2