ERROR 1055 (42000): expression # 1 of the SELECT list is not in the GROUP BY clause ... this is incompatible with sql_mode = only_full_group_by

Question

I am using MYSQL 5.7. I want to get the last lines with excellent device_id . I tried these queries:

Request 1

 SELECT `table`.`id`, `table`.`device_id` FROM `table` WHERE (id IN (SELECT id FROM (SELECT * FROM table ORDER BY date_modified DESC) AS last_modified GROUP BY device_id) and device_id <> ''); +----+------------------+ | id | device_id | +----+------------------+ | 5 | ffcecafe5eed4fba | | 6 | ffcecafe5eed4fba | | 8 | 71085f00e527bae0 | +----+------------------+ 3 rows in set (0.00 sec) 

But this does not remove duplicates.

Subquery 1

 SELECT id FROM (SELECT * FROM table ORDER BY date_modified DESC) AS last_modified GROUP BY device_id; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'last_modified.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 

This gave an error. Then I found on the MySQL website to use ANY_VALUE () to remove this error.

Subquery 2

 SELECT ANY_VALUE(id) FROM (SELECT * FROM table ORDER BY date_modified DESC) AS last_modified GROUP BY device_id; +---------------+------------------+ | ANY_VALUE(id) | device_id | +---------------+------------------+ | 7 | | | 8 | 71085f00e527bae0 | | 5 | ffcecafe5eed4fba | +---------------+------------------+ 3 rows in set (0.00 sec) 

This gives excellent identifiers. But when I use ANY_VALUE in query 1 above, it gives the same result.

How to query individual last rows in MySQL 5.7?

---

Possible duplicate

MySQL 5.7 returns all columns of a table based on a single column

Answer 1

This is a query that worked for me -

 SELECT m1.* FROM messages m1 LEFT JOIN messages m2 ON (m1.name = m2.name AND m1.id < m2.id) WHERE m2.id IS NULL; 

Thanks @Shadow for the link to a possible duplicate .