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?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
