DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(val INT SIGNED NOT NULL
,date TIMESTAMP NOT NULL
,type CHAR(1) NOT NULL
,extra TINYINT NOT NULL
,PRIMARY KEY(val,date,type)
);
INSERT INTO my_table VALUES
(-1,'2014-04-10 00:00:00','A',40),
( 1,'2014-04-15 00:00:00','A',15),
( 2,'2014-04-12 00:00:00','A',77),
( 1,'2014-04-11 00:00:00','A', 2),
( 1,'2014-04-14 00:00:00','A',22),
( 1,'2014-04-10 00:00:00','B',40),
( 1,'2014-04-15 00:00:00','B',15),
( 1,'2014-04-12 00:00:00','B',77),
( 1,'2014-04-11 00:00:00','B', 2),
( 1,'2014-04-14 00:00:00','B',22),
( 4,'2014-04-10 00:00:00','C',40),
( 3,'2014-04-15 00:00:00','C',15),
( 3,'2014-04-12 00:00:00','C',77),
( 1,'2014-04-11 00:00:00','C', 2),
( 3,'2014-04-14 00:00:00','C',22);
SELECT a.*
FROM my_table a
JOIN
( SELECT x.val
, x.type
, MIN(x.date) date
FROM my_table x
JOIN
( SELECT MIN(val) val
, type
FROM my_table
WHERE extra > 12
AND val > 0
GROUP
BY type
) y
ON y.type = x.type
AND y.val = x.val
WHERE x.extra > 12
GROUP
BY val
, type
) b
ON b.val = a.val
AND b.type = a.type
AND b.date = a.date;
+
| val | date | type | extra |
+
| 1 | 2014-04-14 00:00:00 | A | 22 |
| 1 | 2014-04-10 00:00:00 | B | 40 |
| 3 | 2014-04-12 00:00:00 | C | 77 |
+