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.
>Solution :
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);