Once more I am surprised by the ordering that I get from 'GROUP BY'.
This defines the table of directors that have been on the board:
CREATE TABLE DIRECTOR
( Chosen DATE NOT NULL
, Through DATE NOT NULL
, MemberID INTEGER REFERENCES MemberAddress (MemberID)
, CONSTRAINT dpk PRIMARY KEY (Chosen, MemberID)
, Rank TINYINT REFERENCES MemberName (Rank)
)
This query, based also on a view ('offboard') that joins this table with name&address
tables, lists the boards that arise from the table:
select "When", COUNT(givenname || ' ' || surname) AS directors, group_concat(givenname ||
' ' || surname ORDER BY Surname) AS Board
FROM (select distinct chosen AS "When"
FROM director
UNION select distinct ADDDATE(through, 1)
FROM director
WHERE through < CURDATE()) as B JOIN offboard ON "When" between chosen and through
GROUP by "When"
It is only roughly, not completely, ordered by '"When"'. Why? When is 'GROUP-BY' ordering
complete?
