1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126
| --------------
SET AUTOCOMMIT = 0
--------------
--------------
START TRANSACTION
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `test`
--------------
--------------
CREATE TABLE `test`
( `id` integer unsigned NOT NULL AUTO_INCREMENT primary key,
`col1` varchar(255) NOT NULL,
`col2` varchar(255) NOT NULL,
`datime` datetime NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
INSERT INTO `test` (`col1`,`col2`,`datime`) VALUES
('AZERTY', 'France', '2016/03/11 18:59'),
('AZERTY', 'Belgique', '2015/11/06 16:11'),
('AZERTY', 'Suisse', '2015/12/18 16:10'),
('AZERTY', 'xxx', '2016/02/09 11:30'),
('AZERTY', 'zer', '2016/02/09 16:21'),
('AZERTY', 'er', '2015/11/24 15:24'),
('AZERTY', 'df', '2016/01/06 14:38'),
('AZERTY', 'sdgf', '2016/01/06 14:37'),
('AZERTY', 'sdf', '2015/11/10 10:31'),
('SDRTYUI', 'zert', '2015/12/18 16:11'),
('SDRTYUI', 'zert', '2016/02/12 09:27'),
('SDRTYUI', 'zerty', '2016/02/12 09:28'),
('SDRTYUI', 'zerty', '2015/11/19 13:36'),
('SDRTYUI', 'sdfg', '2016/03/10 16:01'),
('SDRTYUI', 'xcfg', '2015/11/24 14:29'),
('SDRTYUI', 'erty', '2015/11/19 15:04'),
('SDRTYUI', 'azert', '2015/12/11 14:28'),
('SDRTYUI', 'sdfg', '2015/11/13 16:17'),
('SDRTYUI', 'iuytr', '2016/01/06 14:29'),
('SDRTYUI', 'zefg', '2016/02/08 14:48'),
('AZSDFGH', 'iuytre', '2015/10/06 08:27'),
('AZSDFGH', 'ratzyerhzs', '2015/10/06 08:17'),
('AZSDFGH', 'sdfg', '2016/02/05 14:11'),
('AZSDFGH', 'gzeztze', '2015/10/07 07:14'),
('AZSDFGH', 'fqfz', '2015/10/07 07:21'),
('AZSDFGH', 'gztgztg', '2015/10/07 07:29'),
('AZSDFGH', 'gzfgzz', '2015/10/07 07:38'),
('AZSDFGH', 'gzgzgz', '2015/10/07 07:48')
--------------
--------------
select * from test
--------------
+----+---------+------------+---------------------+
| id | col1 | col2 | datime |
+----+---------+------------+---------------------+
| 1 | AZERTY | France | 2016-03-11 18:59:00 |
| 2 | AZERTY | Belgique | 2015-11-06 16:11:00 |
| 3 | AZERTY | Suisse | 2015-12-18 16:10:00 |
| 4 | AZERTY | xxx | 2016-02-09 11:30:00 |
| 5 | AZERTY | zer | 2016-02-09 16:21:00 |
| 6 | AZERTY | er | 2015-11-24 15:24:00 |
| 7 | AZERTY | df | 2016-01-06 14:38:00 |
| 8 | AZERTY | sdgf | 2016-01-06 14:37:00 |
| 9 | AZERTY | sdf | 2015-11-10 10:31:00 |
| 10 | SDRTYUI | zert | 2015-12-18 16:11:00 |
| 11 | SDRTYUI | zert | 2016-02-12 09:27:00 |
| 12 | SDRTYUI | zerty | 2016-02-12 09:28:00 |
| 13 | SDRTYUI | zerty | 2015-11-19 13:36:00 |
| 14 | SDRTYUI | sdfg | 2016-03-10 16:01:00 |
| 15 | SDRTYUI | xcfg | 2015-11-24 14:29:00 |
| 16 | SDRTYUI | erty | 2015-11-19 15:04:00 |
| 17 | SDRTYUI | azert | 2015-12-11 14:28:00 |
| 18 | SDRTYUI | sdfg | 2015-11-13 16:17:00 |
| 19 | SDRTYUI | iuytr | 2016-01-06 14:29:00 |
| 20 | SDRTYUI | zefg | 2016-02-08 14:48:00 |
| 21 | AZSDFGH | iuytre | 2015-10-06 08:27:00 |
| 22 | AZSDFGH | ratzyerhzs | 2015-10-06 08:17:00 |
| 23 | AZSDFGH | sdfg | 2016-02-05 14:11:00 |
| 24 | AZSDFGH | gzeztze | 2015-10-07 07:14:00 |
| 25 | AZSDFGH | fqfz | 2015-10-07 07:21:00 |
| 26 | AZSDFGH | gztgztg | 2015-10-07 07:29:00 |
| 27 | AZSDFGH | gzfgzz | 2015-10-07 07:38:00 |
| 28 | AZSDFGH | gzgzgz | 2015-10-07 07:48:00 |
+----+---------+------------+---------------------+
--------------
select *
from test as tb1
where datime = (select max(datime) from test as tb2 where tb2.col1 = tb1.col1)
group by col1
order by col1
--------------
+----+---------+--------+---------------------+
| id | col1 | col2 | datime |
+----+---------+--------+---------------------+
| 1 | AZERTY | France | 2016-03-11 18:59:00 |
| 23 | AZSDFGH | sdfg | 2016-02-05 14:11:00 |
| 14 | SDRTYUI | sdfg | 2016-03-10 16:01:00 |
+----+---------+--------+---------------------+
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager