mysql – How correct is select and how to do it via Join?

Question:

Table and dump:

CREATE TABLE IF NOT EXISTS `ftpuploadermon` (
  `date` datetime(3) DEFAULT NULL,
  `event` text,
  `region` text,
  `host` text,
  `type` text,
  `info` text,
  `status` text,
  `timestamp` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ftpuploadermon` (`date`, `event`, `region`, `host`, `type`, `info`, `status`, `timestamp`) VALUES
('2016-10-14 17:01:26.974', 'WARN ', 'SZ', 'SIU02', '[main]', 'Started, locked port 6696', '0', '2016-10-14 16:19:39.241'),
('2016-10-14 16:58:23.946', 'WARN ', 'SZ', 'SIU02', '[main]', 'Stop signal !', '1', '2016-10-14 16:17:01.335'),
('2016-10-14 16:58:23.946', 'WARN ', 'POV', 'SIU02', '[main]', 'Stop signal !', '1', '2016-10-14 16:17:39.851');

I select the following select from it:

SELECT a.date,a.region,a.host,a.type,a.info,a.status 
FROM ftpuploadermon a, 
( SELECT info,type,timestamp, max(date) as time FROM ftpuploadermon GROUP BY type ) b 
WHERE a.type = b.type AND a.date = b.time AND a.status <> '0' 
ORDER BY a.`date` DESC

The point of select is to select the maximum time value grouped by type . But the sample result is not exactly what you need.

That is, it produces an empty selection result. And I had to choose the maximum value according to the condition for which status <> 0 That is, the result should be:

('2016-10-14 16:58:23.946', 'WARN ', 'POV', 'SIU02', '[main]', 'Stop signal !', '1', '2016-10-14 16:17:39.851');

In general, it’s probably better to do it all somehow through JOIN, but I don’t figure out how yet.

Answer:

c join

SELECT a.date,a.region,a.host,a.type,a.info,a.status 
FROM ftpuploadermon a
   inner join ( SELECT info,type,timestamp, max(date) as time 
                FROM ftpuploadermon 
                where a.status <> '0'
                GROUP BY  info,type,timestamp
              ) b  on a.type = b.type AND a.date = b.time
ORDER BY a.`date` DESC