SELECT with MAX

[lannsjo]

Hi everyone,
This select question has been bothering me for a long time now.

SELECT t1.artnr, t2.photo, t1.access
FROM lundhags_product AS t1
LEFT JOIN lundhags_photos AS t2 ON t1.uid=t2.product_uid
WHERE t1.access <= 1
GROUP BY t1.uid
ORDER BY t1.artname

Problem is that I have many photos connected to one product and I want
to fetch the photo with the newest creation_date.
With this question I get (for some reason) the photo that has the
oldest creation_date.

What I want is to put in something like this in the question:
WHERE t2.creation_date = MAX(t2.creation_date)
but that doesnt seem to work.

Can someone help me!
/Martin

[hoonew]

Well, here's my shot at it:
If you're using MySQL 4.1 or later, you could use a subquery, ie replace
the lundhags_photos below with:
(SELECT * FROM lundhags_photos ORDER BY creation_date DESC)
This worked when I tested it, I don't know if this behavior is
predictable.

Otherwise, you may have to use a temporary table? I'm curious to see
what others have to say...

lannsjo wrote:

Hi everyone,
This select question has been bothering me for a long time now.

SELECT t1.artnr, t2.photo, t1.access
FROM lundhags_product AS t1
LEFT JOIN lundhags_photos AS t2 ON t1.uid=t2.product_uid
WHERE t1.access <= 1
GROUP BY t1.uid
ORDER BY t1.artname

Problem is that I have many photos connected to one product and I want
to fetch the photo with the newest creation_date.
With this question I get (for some reason) the photo that has the
oldest creation_date.

What I want is to put in something like this in the question:
WHERE t2.creation_date = MAX(t2.creation_date)
but that doesnt seem to work.

Can someone help me!
/Martin