Skip to main content

Posts

Showing posts from March, 2014

Select Newset or Most Updated Records from a MySQL Table - Two options

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