List:General Discussion« Previous MessageNext Message »
From:hsv Date:April 8 2011 12:51pm
Subject:Ordering by grouping
View as plain text  
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?

Thread
Ordering by groupinghsv8 Apr
  • Re: Ordering by groupingMichael Dykman8 Apr
    • Re: Ordering by groupinghsv10 Apr