only_full_group_by : "ORDER BY clause is not in GROUP BY clause"

Question

Issue of mysql new version with only_full_group_by

I need to get table value using Group by (col_id) and order by (date) and my code in mysql as following :

SELECT COUNT(created_date) AS `Rows` , ANY_VALUE(id) 
FROM `table` where `my_id` = 1 AND `status` = '0' 
GROUP BY `id` 
ORDER BY `created_date` DESC

I'm Getting error as follow

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'database.table.created_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Any one say the solution ?

Thanks in advance...

disable the only_full_group_by `mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';` — Abhishek Sharma, Oct 11 '17 at 11:55
Possible duplicate of [Error related to only\_full\_group\_by when executing a query in MySql](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) — usman ikram, Oct 11 '17 at 11:55
The whole point of the `GROUP BY` errors are that they signal logic errors. It is not possible to `ORDER BY created_date` because there is no such expression in the `SELECT` clause and each group probably contains two or more different values for `created_date`. Which one to use for sorting? Disabling the `sql_mode` doesn't solve the problem, it just makes MySQL return indeterminate results. — axiac, Dec 07 '17 at 19:11

score 0 · Answer 1 · answered Oct 11 '17 at 11:58

With "only full group by", the order by cannot be an arbitrary column. The order by is parsed after the group by, so only aggregaton keys and aggregated columns are allowed.

You could use:

SELECT COUNT(created_date) AS `Rows`, ANY_VALUE(id)
FROM `table` 
WHERE `my_id` = 1 AND `status` = '0'
GROUP BY `id`
ORDER BY ANY_VALUE(`created_date`) DESC;

If I had to guess, you actually want MAX():

ORDER BY MAX(`created_date`) DESC;

only_full_group_by : "ORDER BY clause is not in GROUP BY clause"

1 Answers1