Hi Nikolaus!

I've wanted sorting lists in Firebird myself, but to no avail.

On http://www.firebirdsql.org/refdocs/langrefupd21-aggrfunc-list.html, I
read: "The ordering of the list values is undefined"

And, surely enough, your ORDER BY doesn't actually contribute to the
order of the list - you can easily see this for yourself by removing the
order by or adding DESC - the list is still identical (I tested on
2.5.3, I think).

That being said, to me it seems like the list actually is sorted by
datum (although I would consider this coincidental, and nothing you
ought to rely upon), but that this somehow is changed once you add
another LIST to your query. Adding another level of CTE actually gets
you the order you want:

WITH UNSORTEDDATE AS (
SELECT (CASE Extract (WEEKDAY
FROM x.DATUM) WHEN 1 THEN '[MO]' WHEN 2 THEN '[DI]' WHEN 3 THEN '[MI]' WHEN
4 THEN '[DO]' WHEN 5 THEN '[FR]' WHEN 6 THEN '[SA]' WHEN 0 THEN '[SO]' END)
AS DATESTRING, x.DATUM,
x.RES_ID, x.BAUSTELLE, x.WOCHE, x.KAPAZITAET,
x.ANMERKUNG
FROM ARBEITSEINTEILUNG AS x),
DateStringList AS (
SELECT WOCHE,
LIST(DATESTRING) as DATESTRING,
RES_ID,
BAUSTELLE
FROM UNSORTEDDATE
WHERE WOCHE = '2016-15' AND BAUSTELLE = 'URLAUB'
GROUP BY WOCHE, RES_ID, BAUSTELLE)
SELECT dsl.WOCHE, dsl.DATESTRING, list(sd.ANMERKUNG), dsl.RES_ID,
dsl.BAUSTELLE, AVG(sd.KAPAZITAET)
FROM UNSORTEDDATE sd
JOIN DateStringList dsl
ON sd.WOCHE = dsl.WOCHE
AND sd.RES_ID = dsl.RES_ID
AND sd.BAUSTELLE = dsl.BAUSTELLE
GROUP BY dsl.WOCHE, dsl.DATESTRING, dsl.RES_ID, dsl.BAUSTELLE
ORDER BY dsl.WOCHE, dsl.RES_ID

Another, more reliable way to get your list sorted, would be to use
EXECUTE BLOCK and FOR SELECT to build your list manually. I would have
loved ORDER BY to be part of the LIST function, though it certainly does
not work in Firebird 2.5 and I haven't seen it in the release notes of
Firebird 3 (though I've only done a quick search and never actually used
Firebird 3 myself.

I also tried LIST(distinct DATESTRING) (just for testing). To my great
surprise, this doesn't only change the ordering, but also adds lots of
spaces, so the result changes from:

[MO],[DI],[MI],[DO],[FR]

to:

[DI] ,[DO] ,[FR] ,[MI] ,[MO]

HTH,
Set