MySQL Query issue

[Sonnenblume]

Hey Guys

Ive got 2 tables, one containing a list of images, and one containing all the moderation actions contained on these images. These tables are called "images" and "journal_mod_log" respectively. My aim is to create a list each images most recent moderation action. The problem im having is this.....

Initially I tried just grabbing the data out using the query below, which produced a list of non distinct values...

select images.image_title, journal_mod_log.reason, journal_mod_log.timestamp from
images
join journal_mod_log on images.image_id = journal_mod_log.image_id;

the result of which was this...

<img src="http://www.sonnenblume.co.uk/pictures/withoutGroupBy.gif">

I thought "thats ok, I'll just use a group by clause for images.image_id, and use max to get the most recent timestamp, thus getting the most recent moderation action. Or so I thought. The query I used was this (which produced the following result)

select images.image_title, journal_mod_log.reason, max(journal_mod_log.timestamp)
from
images
join journal_mod_log on images.image_id = journal_mod_log.image_id
group by(images.image_id);

<img src="http://www.sonnenblume.co.uk/pictures/withGroupBy.gif">

Essentially the group by clause seems to be taking the most first instance stored of image_id, which I thought I could overcome by specifying to select the maximum value of timestamp for that image_id, but that doesnt seem to be the case. Does anyone know how to overcome this?

[phranque]

maybe this would work:

select images.image_title, journal_mod_log.reason, journal_mod_log.timestamp
from
images
join journal_mod_log on images.image_id = journal_mod_log.image_id
where max(journal_mod_log.timestamp)
group by(images.image_id);