Tricky SQL query (simultaneous order by and group by)

[eluser]sszynrae[/eluser]
Hoping someone here might give me some help, this resulted in a very unproductive saturday night

Basically I have a typical comments db with commentid, id (of the post it's linked to) etc.
I want to get the latest comments, but list only one from each id.
The problem is that once you group by id the the elements inside the group ignore sort by date as I can only get the first comment from each post.

This is my current query.

Code:

$this->db->select('date,name,id,cid');
$this->db->group_by('id');
$this->db->orderby('date', "desc");
$output = $this->db->get('comments', $limit);

Any ideas? I've tried loads of different variants but none seem to work.

[eluser]sszynrae[/eluser]
Never mind. Found after some more googling, that this might be an mysql bug. Or at least so says a post from april last year. thought i had the latest xampp. oh well.

At any rate someone suggested this workaround.

Code:

$output = $this->db->query('SELECT * FROM ( SELECT * FROM comments order by date desc ) b group by id desc limit '.$limit);

It works..

edit: bleh no it doesn't. it displays the latest comment from each post this time, but it still favours newer posts..

[eluser]bscott[/eluser]
Assuming latest comment has max(comment_id), you could try:-

select * from comment a join
(select post_id,max(comment_id) comment_id from comment group by post_id) b
on a.post_id=b.post_id and a.comment_id=b.comment_id

[eluser]sszynrae[/eluser]
It does indeed. That did the trick. Thank you very much : )