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