How to get the maximum in time and unique in the field value?

Question

MySQL

I have a table with the following data:

INSERT INTO `dch_statistic` (`agent_name`, `id_server`, `event`, `info`, `time`) VALUES
('collector', 'mrp_server', 'error', 'crash file', '2016-09-02 11:37:30'),
('collector', 'mrp_server', 'info', 'done file', '2016-09-02 11:38:06'),
('collector2', 'mrp_server', 'info', 'done file', '2016-09-02 11:38:23'),
('collector2', 'mrp_server', 'info', 'done file', '2016-09-02 11:38:41'),
('collector2', 'mrp_server', 'error', 'error file', '2016-09-02 11:39:04');

How to select from it the entry containing the maximum time for each `agent_name` c `info` = info
That is, you need to end up with 2 lines from the example:
'collector', 'mrp_server', 'info', 'done file', ' 2016-09-02 11:38:06'
'collector2', 'mrp_server', 'info', 'done file', '2016-09-02 11:38:41'

Answer 1

SELECT a.*
FROM dch_statistic a,
(
SELECT agent_name, max(time) as time FROM dch_statistic GROUP BY agent_name
) b
WHERE a.agent_name = b.agent_name
AND a.time = b.time