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
| --------------
START TRANSACTION
--------------
--------------
set session collation_connection = "latin1_general_ci"
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE IF NOT EXISTS `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `tbldefi`
--------------
--------------
CREATE TABLE `tbldefi`
( `id` integer unsigned NOT NULL auto_increment primary key,
`username` varchar(255) NOT NULL,
`monrecord` integer unsigned NOT NULL,
`classe` smallint unsigned NOT NULL,
`lvl` tinyint unsigned NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
INSERT INTO `tbldefi` (`username`,`monrecord`,`classe`,`lvl`) values
('sandjana', 46069, 604, 0),
('loan', 42917, 604, 0),
('vincent', 28839, 604, 0),
('loeva', 43737, 604, 0),
('toto', 29637, 604, 0),
('sandjana', 50310, 604, 1),
('loan', 87342, 604, 1),
('vincent', 36812, 604, 1),
('loeva', 53962, 604, 1),
('sandjana', 54323, 604, 2),
('loeva', 53900, 604, 2)
--------------
--------------
select * from `tbldefi`
--------------
+----+----------+-----------+--------+-----+
| id | username | monrecord | classe | lvl |
+----+----------+-----------+--------+-----+
| 1 | sandjana | 46069 | 604 | 0 |
| 2 | loan | 42917 | 604 | 0 |
| 3 | vincent | 28839 | 604 | 0 |
| 4 | loeva | 43737 | 604 | 0 |
| 5 | toto | 29637 | 604 | 0 |
| 6 | sandjana | 50310 | 604 | 1 |
| 7 | loan | 87342 | 604 | 1 |
| 8 | vincent | 36812 | 604 | 1 |
| 9 | loeva | 53962 | 604 | 1 |
| 10 | sandjana | 54323 | 604 | 2 |
| 11 | loeva | 53900 | 604 | 2 |
+----+----------+-----------+--------+-----+
--------------
SELECT t1.*
FROM tbldefi AS t1
WHERE t1.monrecord = ( SELECT min(t2.monrecord)
FROM `tbldefi` as t2
WHERE t2.lvl = t1.lvl
)
ORDER BY t1.lvl
--------------
+----+----------+-----------+--------+-----+
| id | username | monrecord | classe | lvl |
+----+----------+-----------+--------+-----+
| 3 | vincent | 28839 | 604 | 0 |
| 8 | vincent | 36812 | 604 | 1 |
| 11 | loeva | 53900 | 604 | 2 |
+----+----------+-----------+--------+-----+
--------------
COMMIT
--------------
Appuyez sur une touche pour continuer... |
Partager