Hello,

I am using Firebird 2.5.5. and see following behaiviour:

1.       One table is used to get the weekdays in a list

2.       The other table is joined to identify those working schedules for these days

3.       If the second table has a value in the field ANMERKUNG the sorting order inside List() changes:

a.       Without ANMERKUNG – thats the expected result:

WOCHE LIST        RES_ID  BAUSTELLE         AVG

2016-15                [MO],[DI],[MI],[DO],[FR]              12           URLAUB               1

2016-15                [MO],[DI],[MI],[DO],[FR]              34           URLAUB               1

b.       With ANMERKUNG – the day with ANMERKUNG IS sorted first:

WOCHE LIST        LIST1      RES_ID BAUSTELLE         AVG

2016-15                [DI],[MO],[MI],[DO],[FR]              Hallo      12           URLAUB              1

2016-15                [DO],[MO],[DI],[MI],[FR]              Irgendwo            34           URLAUB              1

Any idea how to overcome this?

Thanks

Niko

This ist the complete query:

WITH SORTEDDATE 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.RES_ID, x.BAUSTELLE, x.WOCHE, x.KAPAZITAET,

x.ANMERKUNG

FROM ARBEITSEINTEILUNG AS x

ORDER BY x.DATUM)

SELECT WOCHE,

LIST(DATESTRING),

--LIST(ANMERKUNG  ), -- This changes the order of sorting inside the CTE

RES_ID,

BAUSTELLE,

AVG(KAPAZITAET)

FROM SORTEDDATE

WHERE WOCHE = '2016-15' AND BAUSTELLE = 'URLAUB'

GROUP BY

WOCHE, RES_ID, BAUSTELLE

ORDER BY WOCHE, RES_ID