Hello Set, thank you very much for your support!
I was aware of the fact, that LIST() is unsorted, but I did not expect the problem I described in this posting. It works very nice. Thanks again Niko -----Ursprüngliche Nachricht----- Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Samstag, 23. April 2016 17:36 An: firebird-support@yahoogroups.com Betreff: Re: [firebird-support] Sorting CTE for List() 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 ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ------------------------------------ Yahoo Groups Links
