[Solved] MySQL error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

This error is mainly because the fields in the content of the select do not appear in the group by

Here are two solutions:

The first:

Add a line to your MySQL my.ini file

[mysqld]
sql_mode = ‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’

The second:

I would have used

SELECT
 *
FROM
 biz_table bt
 LEFT JOIN biz_table_ver btv ON bt.table_id = btv.table_id
WHERE
 bt.system_up = 1
GROUP BY
 bt.table_name

This spelling error

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘zx_ds.biz_table_ver.ver_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by\\
### The error may exist in URL [jar:file:/opt/zxs/ds/ds-dict.jar!/BOOT-INF/classes!/mapper/system/BizTableMapper.xml]\\
### The error may involve com.gyjn.dict.data.mapper.BizTableMapper.selectBizTableList-Inline\\
### The error occurred while setting parameters\\
### SQL: select * FROM biz_table bt LEFT JOIN ( SELECT * FROM biz_table_ver GROUP BY table_id ) btv ON bt.table_id = btv.table_id WHERE bt.system_up=1\\
### Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘zx_ds. biz_table_ver.ver_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by\\
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘zx_ds.biz_table_ver.ver_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by”

later changed to

SELECT
*
FROM
biz_table_ver v
INNER JOIN biz_table t
INNER JOIN ( SELECT max( ver_id ) ver_id, table_id FROM biz_table_ver GROUP BY table_id ) maxv ON v.ver_id = maxv.ver_id
AND t.table_id = maxv.table_id

just fine