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...

Answer 1

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;