How to resolve “ORDER BY clause is not in SELECT list” caused MySQL 5.7 with SELECT DISTINCT and ORDER BY

Cpanel hosting Solution Database Dedicated Server Solution

If you have control of the server and you are running legacy code you can’t easily change, you can adjust the SQL mode of the server and remove “only_full_group_by” either for the duration of boot, by running the query

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));'

or by adding sql_mode='' to your my.cnf file.

Obviously its better to change your code if you have the possibility, but if not, this will disable that warning.

  • For reference, if you go the route of adding sql_mode='' to your my.cnf file, be sure to add this line under a new line with the [mysqld] heading. For example: [mysqld] (new line) sql_mode = ''. Otherwise, you’ll get an error when attempting to restart mysql, like: Job for mysql.service failed because the control process exited with error code.

Leave a Reply

Your email address will not be published. Required fields are marked *