Monthly Archives: December 2017

Error: MySQL 5.7 이상 Group by – this is incompatible with sql_mode=only_full_group_by

MySQL 5.7이상에서 Group By 쿼리 사용시 발생하는 오류

0 12 12:27:17 select tp1.idx, tp1.category, tp1.contents, tp1.view_yn, tp1.status, tp1.recordid, max(tp1.recordtime)
  from TBL_PAGE tp1 group by tp1.RECORDTIME
  LIMIT 0, 1000 Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'solarbridge_dev.tp1.IDX' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 0.000 sec	Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0 12 12:27:17 select tp1.idx, tp1.category, tp1.contents, tp1.view_yn, tp1.statu' at line 5	0.000 sec


Error Code: 1140. In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'wordpressdb_test.TC.IDX'; this is incompatible with sql_mode=only_full_group_by

 

Solution.1)
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Solution.2)
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

ANY_VALUE라고 싸줌.