Select all with MAX id in MySQL-CodePudding

I have a table machines and a table machine_logs, every 5 hours new logs are inserted in the logs table, so for example: the machine with ID 7 have a lot of logs but I only need the last one.

I have this query:

SELECT MAX(id), machine_id FROM machine_logs GROUP BY machine_id;

But when I want to SELECT more columns, MySQL throws this error:

SELECT MAX(id), total, machine_id FROM machine_logs GROUP BY machine_id; 

[42000][1055] Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.machine_logs.total' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

How can I achieve this without changing the sql_mode? Is there any other way to get only the most recent ID in every case? Remember that the query returns a lot of results, not just 1.

EDIT: There are around 400 machines, every machine has logs but I only need the most recent one. The output will have 400 rows, every most recent log associated to every machine. Every machine has 100 logs.

CodePudding user response：

You can select more data with subquery as follows:


SELECT id, total, machine_id FROM
Machine_logs where id in (select max(id) from machine_logs GROUP BY machine_id);