Firebird: how to arrange list aggregate?

Question

Question:

In the next query, I want the equivalent for

GROUP_CONCAT(ctAncestors.ancestor ORDER BY ctAncestors.depth DESC) AS breadcrumbs_id 

in firebird.

So far i got

 LIST(ctAncestors.ancestor, ',') AS breadcrumbs_id 

but I can't figure out how to add order to the list directive.

How can I place an order in a List aggregate?

 -- http://www.unlimitedtricks.com/sorting-a-subtree-in-a-closure-table-hierarchical-data-structure/ -- DECLARE @__in_rootnodeId AS bigint -- SET @__in_rootnodeId = 8 -- SET @__in_rootnodeId = 1 SELECT COALESCE(ctDescendants.ancestor, 0) AS parent_id ,ctAncestors.descendant AS child_id ,tClosureItemsTable.COM_Id ,tClosureItemsTable.COM_Text --,'ab' as breadcrumbs --,LIST(ctAncestors.ancestor ORDER BY ctAncestors.depth DESC) AS breadcrumbs_id ,LIST(ctAncestors.ancestor, ',') AS breadcrumbs_id --,GROUP_CONCAT(ctAncestors.ancestor ORDER BY ctAncestors.depth DESC) AS breadcrumbs_id -- ,GROUP_CONCAT(breadcrumb_data.COM_Text ORDER BY ctAncestors.depth DESC) AS breadcrumbs --,GROUP_CONCAT(breadcrumb_data.COM_Text ORDER BY ctAncestors.depth) AS breadcrumbs ,LIST(breadcrumb_data.COM_Text) AS breadcrumbs --,GROUP_CONCAT(breadcrumb_data.COM_Text ORDER BY ctAncestors.depth DESC SEPARATOR '-') AS breadcrumbs2 /* , SUBSTRING ( ( SELECT -- breadcrumb.ancestor AS 'text()' -- Remove substring for this -- ', ' + CAST(breadcrumb.ancestor AS nvarchar(36)) AS 'text()' ', ' + CAST(breadcrumb_data.comment AS nvarchar(36)) AS 'text()' FROM T_CommentClosure AS breadcrumb LEFT JOIN Comments AS breadcrumb_data ON breadcrumb_data.COM_Id = breadcrumb.ancestor WHERE (breadcrumb.descendant = ctAncestors.descendant) ORDER BY breadcrumb.depth DESC FOR XML PATH('') ) ,2 ,8000 ) AS breadcrumbs */ , ( SELECT COUNT(*) FROM T_CommentClosure AS tp WHERE tp.ancestor = tClosureItemsTable.COM_Id AND tp.depth = 1 ) AS ChildCount FROM T_CommentClosure AS ctAncestors -- Must be left join, for root node LEFT JOIN T_CommentClosure AS ctDescendants ON (ctDescendants.descendant = ctAncestors.descendant) AND (ctDescendants.depth = 1) -- INNER JOIN just in case item has been somehow deleted when FK disabled INNER JOIN T_Comments AS tClosureItemsTable ON (ctAncestors.descendant = tClosureItemsTable.COM_Id) INNER JOIN T_Comments AS breadcrumb_data ON breadcrumb_data.COM_Id = ctAncestors.ancestor WHERE (1=1) -- AND (ctAncestors.ancestor = @__in_rootnodeId) -- ROOT node id AND ( -- ( ctAncestors.ancestor = @__in_rootnodeId) -- ROOT node id (1=2) OR (1=1) -- (@__in_rootnodeId IS NULL) ) -- AND tClosureItemsTable.active = 1 GROUP BY ctAncestors.descendant ,ctDescendants.ancestor ,tClosureItemsTable.COM_Id ,tClosureItemsTable.COM_Text ORDER BY breadcrumbs ASC -- DESC 

Answer 1

The LIST() documentation states:

The order of the values ​​in the list is undefined.

However, if you use a subquery or common table expression (CTE), which is first ordered using GROUP BY columns and this column, then this will work, however it means that you are dependent on an implementation artifact that can change with point versions or new versions.

There is a ticket for improvement ( CORE-2332 ) in the Tracker Firebird tracker, but it was not planned for the version. This ticket also contains an example workaround:

 WITH EDU_EPT AS ( SELECT EEPT2.TARGET_SWE FROM EDUCATION_EVENT_PR_TRGT EEPT2 WHERE EEPT2.EDUCATION_EVENT_ID = :EDU_EVENT_ID ORDER BY EEPT2.ORDINAL, EEPT2.ID ) SELECT LIST('• ' || EEPT.TARGET_SWE, ASCII_CHAR(13) || ASCII_CHAR(10)) || '.' FROM EDU_EPT EEPT