CW3MWEFp

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 
26FROM ftpuploadermon a
27   inner join ( SELECT info,type,timestamp, max(date) as time 
28                FROM ftpuploadermon 
29                where a.status <> '0'
30                GROUP BY  info,type,timestamp
31              ) b  on a.type = b.type AND a.date = b.time
32ORDER BY a.`date` DESC
33	
34SELECT a.date,a.region,a.host,a.type,a.info,a.status
35FROM ftpuploadermon a, ( SELECT info,type, max(date) as time
36                         FROM ftpuploadermon
37                         WHERE status <> '0'
38                         GROUP BY type ) b
39WHERE a.type = b.type AND a.date = b.time
40ORDER BY a.date DESC