[DB-SIG] Strange resultset with ORDER BY col1, GROUP BY col2

Kolbe Kegel kolbe at kolbekegel.com
Fri Jun 24 09:08:54 CEST 2005


> I just tried something similar. I don't have the same versions as you, 
> so I couldn't even use group_contact. I just created a column I could 
> sum() instead.

I don't reproduce it either with this query on my original dataset 
(with the addition of a column `id` containing  the value 6 

mysql> select * from gctest;
| comment | group | id   |
| test    |     1 |    6 |

Here is the query i used:

select sum(id) as x from gctest group by `group` order by comment;

It simply returned "6", as one would hope it would do.

Let me know if that doesn't capture the spirit of what you were trying 
to do.

Perhaps interesting is the fact that this doesn't cause any "problem" 
either (returns 'testtest'):

SELECT CONCAT(comment,comment) AS comment FROM gctest GROUP BY `group` 
ORDER BY `comment`;

While not using the aggregate function in the field list, it does still 
use the other elements. This must mean that the problem is related to 
the aggregate function, and more specifically to GROUP_CONCAT since the 
problem is not triggered by SUM().

Thanks for looking into it :)


More information about the DB-SIG mailing list