Thursday, March 11, 2010

[Level 2] The side effect of MySQL "group by" clause.

When you use group by clause but not using aggregation function for all selected columns.
The result set will always get the first record, and it will cause data logic fail.
If you want to avoid this situation happens, you can modify the sql_mode with
the value "only_full_group_by".
The sample as the following:

mysql> select * from t;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
|    2 |    3 |
|    2 |    2 |
|    2 |    1 |
+------+------+
6 rows in set (0.00 sec)

mysql> select * from t group by a;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    3 |
+------+------+
2 rows in set (0.00 sec)

mysql> set sql_mode=only_full_group_by;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t group by a;
ERROR 1055 (42000): 'test.t.b' isn't in GROUP BY
mysql> select a, max(b) from t group by a;
+------+--------+
| a    | max(b) |
+------+--------+
|    1 |      3 |
|    2 |      3 |
+------+--------+
2 rows in set (0.00 sec)

mysql> 

Wish this helps.

regards,
Stanley Huang