GROUP BY : getting what I need

I've got a table which contains comments to images. The table has an id field (auto number), a field referencing the image the comment is about, the comment its self and the date of the comment. The table is called comments. I want to select the most recent comment for each image_id. But I can't, I've tried all sorts of GROUP BYs on image_id and used MAX(postdate) or MAX(id) but I can't get the right comment out. I've tried selecting MAX(postdate) AS something and then using HAVING something, but the all have this of course, so I don't know. It doesn't help that MySQL doesn't seem to be able to ORDER BY and then GROUP BY a select.

Can anyone help? There has to be an easy solution to stop me losing sleep...

Try this.

select comment,max(postdate) from comments group by image_id;

That seems to give the latest "postdate", but the first comment. I want to get the comment which corresponds to MAX(postdate), what you suggested was what I tried first - it doesn't seem to be working.

Hmm, odd.

Try this then:

select comment from comments where postdate=max(postdate) group by image_id;

that's another thing i've tried, it produces a really crappy mySQL error which doesn't help. What I really need is a sub-select, its the only way I can join against MAX(postdate), as far as I can see. I was playing at work today, trying to do it in Access (out of desperation) and that has more functions which can be used with GROUP BY, including LAST(), which does what I need it do, i.e. if I do LAST(comment) it gives it to me. mySQL doesn't seem to have anything like this... I know Access can never out-do mySQL - there must be solution, somewhere.

Perhaps if you posted the "crappy mysql error"....

details, details, details if you want help

the message is

MySQL said: Invalid use of group function

not very useful, is it?

Yeah, I had my doubts about whether that would work or not.

That brings us back to the first query. That SHOULD work. I tried it myself and it worked fine... of course, I had to guess at your data structure. If you'd like, email me a schema/data dump of the table and I'll take a look.