s5XXVyPg

1CREATE TABLE IF NOT EXISTS `ftpuploadermon` (
2  `date` datetime(3) DEFAULT NULL,
3  `event` text,
4  `region` text,
5  `host` text,
6  `type` text,
7  `info` text,
8  `status` text,
9  `timestamp` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
10) ENGINE=InnoDB DEFAULT CHARSET=utf8;
11
12INSERT INTO `ftpuploadermon` (`date`, `event`, `region`, `host`, `type`, `info`, `status`, `timestamp`) VALUES
13('2016-10-14 17:01:26.974', 'WARN ', 'SZ', 'SIU02', '[main]', 'Started, locked port 6696', '0', '2016-10-14 16:19:39.241'),
14('2016-10-14 16:58:23.946', 'WARN ', 'SZ', 'SIU02', '[main]', 'Stop signal !', '1', '2016-10-14 16:17:01.335'),
15('2016-10-14 16:58:23.946', 'WARN ', 'POV', 'SIU02', '[main]', 'Stop signal !', '1', '2016-10-14 16:17:39.851');
16	
17SELECT a.date,a.region,a.host,a.type,a.info,a.status 
18FROM ftpuploadermon a, 
19( SELECT info,type,timestamp, max(date) as time FROM ftpuploadermon GROUP BY type ) b 
20WHERE a.type = b.type AND a.date = b.time AND a.status <> '0' 
21ORDER BY a.`date` DESC
22	
23('2016-10-14 16:58:23.946', 'WARN ', 'POV', 'SIU02', '[main]', 'Stop signal !', '1', '2016-10-14 16:17:39.851');
24	
25SELECT a.date,a.region,a.host,a.type,a.info,a.status FROM ftpuploadermon a, ( SELECT info,type, max(date) as time FROM ftpuploadermon WHERE status <> '0' GROUP BY type ) b WHERE a.type = b.type AND a.date = b.time ORDER BY a.date DESC