Select Newset or Most Updated Records from a MySQL Table - Two options
CREATE TABLE ttt(
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
date DATETIME NOT NULL,
aid MEDIUMINT UNSIGNED NOT NULL,
status ENUM('ac', 'na') NOT NULL,
PRIMARY KEY(id)
) ENGINE InnoDB;
INSERT INTO ttt SET id=1, date='2014-03-10', aid=1, status='ac';
INSERT INTO ttt SET id=2, date='2014-03-11', aid=1, status='na';
INSERT INTO ttt SET id=3, date='2014-03-11', aid=2, status='ac';
INSERT INTO ttt SET id=4, date='2014-03-12', aid=1, status='ac';
INSERT INTO ttt SET id=5, date='2014-03-12', aid=2, status='na';
SELECT
ttt.*
FROM
ttt
LEFT JOIN ttt t2 ON t2.aid=ttt.aid AND t2.id>ttt.id
WHERE
t2.id IS NULL;
SELECT ttt.*
FROM ttt
INNER JOIN
(SELECT MAX(id) as maxid, aid FROM tttGROUP BY aid) t2 ON ttt.id = t2.maxid;
And the results:
mysql> SELECT ttt.* FROM ttt LEFT JOIN ttt t2 ON t2.aid=ttt.aid AND t2.id>ttt.id WHERE t2.id IS NULL;
+----+---------------------+-----+--------+
| id | date | aid | status |
+----+---------------------+-----+--------+
| 4 | 2014-03-12 00:00:00 | 1 | ac |
| 5 | 2014-03-12 00:00:00 | 2 | na |
+----+---------------------+-----+--------+
2 rows in set (0.00 sec)
mysql> SELECT ttt.* FROM ttt LEFT JOIN ttt t2 ON t2.aid=ttt.aid AND t2.id>ttt.id WHERE t2.id IS NULL;
+----+---------------------+-----+--------+
| id | date | aid | status |
+----+---------------------+-----+--------+
| 4 | 2014-03-12 00:00:00 | 1 | ac |
| 5 | 2014-03-12 00:00:00 | 2 | na |
+----+---------------------+-----+--------+
2 rows in set (0.00 sec)
