0

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.

irf98
  • 27
  • 4
  • This question is frequent on Stack Overflow. It's usually tagged [tag:greatest-n-per-group]. There are many answers posted. Here's one of mine with a high score: https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql – Bill Karwin Oct 05 '22 at 18:38

1 Answers1

1

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);
MHA
  • 89
  • 4