From: hsv
Date: April 8 2011 12:51pm
Subject: Ordering by grouping
List-Archive: http://lists.mysql.com/mysql/224793
Message-Id:
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
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?