How to order a list aggregate?

Question

In the following 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 

I cannot figure out how to add an order by in the list directive.

How can I do a order by in the 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

Excavation

Answer 1

As the documentation of LIST() states:

The ordering of the list values is undefined.

That said, if you use a subquery or common table expression (CTE) that first orders by the GROUP BY-columns and this column then it will work, however this does mean that you are depending on an implementation artefact which might change with point releases or new versions.

There is an improvement ticket (CORE-2332) in the Firebird tracker for this, but it hasn't been planned for a version. This ticket also contains an example of the 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 

Vaal

Well, you know, the whole point of a closure table is that one doesn't have to use a CTE. – Excavation

@Quandary I don't exactly know what you mean with 'closure table', but this is not a recursive CTE that is usually used for hierarchical queries, but a simple CTE as a replacement of a subquery. – Vaal

This workaround does not work if you have group by clause in your query. – Homesteader

@Homesteader as far as I know it should work, but as it depends on implementation artefacts: no guarantees. You should consider posting a new question about this. – Vaal

Are there any news about this? The issue in the tracker remains open. Have the SQL standards caught up and Firebird perhaps implemented a different construct that achieves the same thing? – Precedent