What to do if select list is not in group by clause and contains nonaggregated column
My SQL statement returns properly on my laptop's MySQL (Server version: 5.5.62-0ubuntu0.14.04.1 - (Ubuntu)) but on my server (Server version: 5.7.26-0ubuntu0.16.04.1 - (Ubuntu)) it returns an error.
SELECT * FROM `orders` WHERE `mail_sent`='No' AND `datetime` < DATE_SUB(NOW(), INTERVAL 15 MINUTE) GROUP BY `contact_id` ORDER BY `datetime` ASC;
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shop.orders.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
A quick fix of select list is not in group by clause and contains nonaggregated column
is to use ANY_VALUE(contact_id) as contact_id instead of only contact_id in your SELECT.
For example:
SELECT ANY_VALUE(contact_id) as contact_id, ANY_VALUE(mail_sent) as mail_sent, ANY_VALUE(datetime) AS datetime WHERE ... GROUP BY contact_id ORDER BY datetime DESC
Note that the field you group by (e.g. contact_id) must be selected with ANY_VALUE(contact_id) AS contact_id.
You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column.
Worth to read:
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html