Ordering by grouping

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/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Ordering by grouping

Mildly surprised this is working at all. Your name alias fields like
"When" should be quoted by back-tics, no double quotes.. this is a
clarification and does not solve the issue really although it might
make your final result less surprising.

A key thing to note about GROUP BY is that, although it uses ordering
internally to do it's work, it is not an ORDER BY clause and does not
pretend to make guarantees as to the order of the resulting data. If
you need ordering, add an ORDER BY clause after your GROUP BY.

- michael dykman

On Fri, Apr 8, 2011 at 8:51 AM, wrote:
> 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
> ( =A0 =A0 =A0 Chosen =A0DATE NOT NULL
> , =A0 =A0 =A0 Through DATE NOT NULL
> , =A0 =A0 =A0 MemberID =A0 =A0 =A0 =A0INTEGER REFERENCES MemberAddress (M=
emberID)
> , =A0 =A0 =A0 CONSTRAINT dpk PRIMARY KEY (Chosen, MemberID)
> , =A0 =A0 =A0 Rank =A0 =A0TINYINT 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_con=
cat(givenname || ' ' || surname ORDER BY Surname) AS Board
> FROM (select distinct chosen AS "When"
> =A0 =A0 =A0 =A0FROM director
> =A0 =A0 =A0 =A0UNION select distinct ADDDATE(through, 1)
> =A0 =A0 =A0 =A0FROM director
> =A0 =A0 =A0 =A0WHERE through < CURDATE()) as B JOIN offboard ON "When" be=
tween chosen and through
> GROUP by "When"
>
> It is only roughly, not completely, ordered by '"When"'. Why? When is 'GR=
OUP-BY' ordering complete?
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Ordering by grouping

>>>> 2011/04/08 17:12 -0400, Michael Dykman >>>>
Mildly surprised this is working at all. Your name alias fields like
"When" should be quoted by back-tics, no double quotes.. this is a
clarification and does not solve the issue really although it might
make your final result less surprising.

A key thing to note about GROUP BY is that, although it uses ordering
internally to do it's work, it is not an ORDER BY clause and does not
pretend to make guarantees as to the order of the resulting data. If
you need ordering, add an ORDER BY clause after your GROUP BY.
<<<<<<<<
I use 'ANSI', and avoid the so-called back-ticks.

This is from the paged HTML help file (note that MySQL allows 'ASC" and 'DESC' after 'GROUP BY'):



12.2.9. SELECT Syntax

.....

If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns.To avoid the overhead of sorting that GROUP BY produces, add ORDER BY NULL....

On Fri, Apr 8, 2011 at 8:51 AM, wrote
> 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/mysql?unsub=gcdmg-mysql-2@m.gmane.org