On my database, I have to show a preview with all the posts with their last content and I don’t really know how to do it.
CREATE TABLE IF NOT EXISTS `posts` ( `id` int(11) NOT NULL auto_increment, `post_id` int(11) NOT NULL, `title` varchar(255) NOT NULL, `body` text NOT NULL, `created` date NOT NULL, PRIMARY KEY (`id`), KEY `post_id` (`post_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ; INSERT INTO `posts` (`id`, `post_id`, `title`, `body`, `created`) VALUES (1, 1, 'hello world', 'this is the first post', '2011-10-10'), (2, 2, 'the second post', 'and this is the second post', '2011-10-10'), (3, 2, 'second post', 'title is modified, because this is a better version', '2011-10-11');
So the result has to be the post with id 1 and 3. The post with id 2, appears twice because there are two versions on different dates. The dates of the posts marks which version is newer.
I need to display the last state of all the post. How can I do that?