How to do a within-group aggregate query in MySQL?

Question

I am trying to get the latest modified "posts", from a group of "posts", that all have the same parent_id. I already learned today that grouping is not the answer, and that I should either use an inner subquery, or a "within-group aggregate", thanks to this site >> Aggregates >> Within-group aggregates. I chose to go with the latest, as I find it nicer, easier to implement given that I use CakePHP, and is said to be faster (can you confirm?)

So I came up with the following query:

SELECT `Post`.`id`, `Post`.`parent_id`, `Post`.`modified` 
FROM `cake_posts` AS `Post` 
LEFT JOIN `cake_posts` AS `SelfPost` ON
      (`Post`.`id` = `SelfPost`.`id` AND `Post`.`modified` < `SelfPost`.`modified`)
WHERE `SelfPost`.`id` IS NULL 
ORDER BY `Post`.`parent_id` ASC

But here is the result:

|-----------------------------------|
| id |parent_id|      modified      |
|-----------------------------------|
| 1  |    1    |2010-10-16 17:04:43 |
| 3  |    1    |2010-10-16 20:04:53 |
| 6  |    2    |2010-10-16 21:46:59 |
| 5  |    2    |2010-10-16 21:46:44 |
| 2  |    2    |2010-10-16 17:06:10 |
| 4  |    4    |2010-10-16 21:46:19 |
| 7  |    7    |2010-10-16 22:03:19 |
| 8  |    8    |2010-10-16 22:03:25 |
| 9  |    9    |2010-10-16 22:03:32 |
| 10 |    10   |2010-10-17 00:18:10 |
| 11 |    11   |2010-10-17 00:18:15 |

And as you can see, I still have duplicates parent_id, so what am I doing wrong?

EDIT: Basically it is a forum only very light, were topics and posts are stored in the same table. So each post (aka message, aka row in the table) either has a different parent, or himself as a parent (but I don't think this is relevant at this point). I am trying to build the index, so for each topic (ie posts with the same parent_id) I want only the latest modified message. What I would like to get is:

|-----------------------------------|
| id |parent_id|      modified      |
|-----------------------------------|
|    |    -    |                    |
| 3  |    1    |2010-10-16 20:04:53 |
| 6  |    2    |2010-10-16 21:46:59 |
|    |    -    |                    |
|    |    -    |                    |
| 4  |    4    |2010-10-16 21:46:19 |
| 7  |    7    |2010-10-16 22:03:19 |
| 8  |    8    |2010-10-16 22:03:25 |
| 9  |    9    |2010-10-16 22:03:32 |
| 10 |    10   |2010-10-17 00:18:10 |
| 11 |    11   |2010-10-17 00:18:15 |

Further data:

CREATE TABLE IF NOT EXISTS `cake_posts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) unsigned NOT NULL DEFAULT '0',
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`, `parent_id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Posts to topics' AUTO_INCREMENT=12 ;

INSERT INTO `cake_posts` (`id`, `parent_id`, `modified`) VALUES
(1, 1, '2010-10-16 17:04:43'),
(3, 1, '2010-10-16 20:04:53'),
(6, 2, '2010-10-16 21:46:59'),
(5, 2, '2010-10-16 21:46:44'),
(2, 2, '2010-10-16 17:06:10'),
(4, 4, '2010-10-16 21:46:19'),
(7, 7, '2010-10-16 22:03:19'),
(8, 8, '2010-10-16 22:03:25'),
(9, 9, '2010-10-16 22:03:32'),
(10, 10, '2010-10-17 00:18:10'),
(11, 11, '2010-10-17 00:18:15');

Answer 1

Well, after much trying and frustration, I finally figured out what was going wrong. A simple mistake actually, I was joining on the wrong field. Because, since I wanted to group By parent_id initially, I should have realized that it was also by parent_id that I had to join, and not by id. So here is the corrected query:

SELECT `Post`.`id`, `Post`.`parent_id`, `Post`.`modified` 
FROM `cake_posts` AS `Post` 
LEFT JOIN `cake_posts` AS `SelfPost` ON
      (`Post`.`parent_id` = `SelfPost`.`parent_id` AND `Post`.`modified` < `SelfPost`.`modified`)
WHERE `SelfPost`.`id` IS NULL 
ORDER BY `Post`.`modified ` DESC