Workaround of lacking of ORDER BY when using GROUP_CONCAT in Netezza

Question

In Netezza, GROUP_CONCAT function does not support ORDER BY, it uses its own internal order by, which is order by the column you use as parameter.

SELECT 'GROUP BY '||TOOLKIT.SQLEXT.GROUP_CONCAT(PRIMARY_KEY, ', ') AS GROUP_BY
FROM (
SELECT 1 AS SEQ, 'DATA_DATE' AS PRIMARY_KEY
UNION ALL
SELECT 2 AS SEQ, 'ACCT_ID'   AS PRIMARY_KEY
) S;

This will return:

      GROUP_BY
GROUP BY ACCT_ID, DATA_DATE

But how to get result order by SEQ instead of PRIMARY_KEY to get result like this:

      GROUP_BY
GROUP BY DATA_DATE, ACCT_ID

Answer 1

We can add SEQ at beginning of first GROUP_CONCAT parameter to force internal ORDER BY to work for us, and then remove what we have added. In order to remove safely, we added extra '@' at beginning and end of the sequence.

SELECT 'GROUP BY '||TOOLKIT.SQLEXT.REGEXP_REPLACE(
                    TOOLKIT.SQLEXT.GROUP_CONCAT('@'||LPAD(SEQ, 4, '0')||'@'
                    ||PRIMARY_KEY, ', '), 
                    '@[0-9]{4}@', '') AS GROUP_BY
FROM (
SELECT 1 AS SEQ, 'DATA_DATE' AS PRIMARY_KEY
UNION ALL
SELECT 2 AS SEQ, 'ACCT_ID'   AS PRIMARY_KEY
) S;

This will return what we want:

GROUP_BY
GROUP BY DATA_DATE, ACCT_ID