LifeinAsia - 3:36 pm on Sep 13, 2010 (gmt 0)
Hi and welcome to WebmasterWorld [webmasterworld.com]!
Off the top of my head, I would do probably it the following way (others may have a different method). Also, this is written from a MS SQL perspective- you may need to do some tweaking for MySQL.
First, you want to find the latest picture for each category:
SELECT pics.cat_id, MAX(DateUpdated) AS MaxDate
GROUP BY pics.cat_id
Now use that as the basis for your main query:
SELECT pics.*, Cats.*
FROM (SELECT pics.cat_id, MAX(DateUpdated) AS MaxDate FROM pics GROUP BY pics.cat_id) AS PicMax INNER JOIN pics ON (PixMax.cat_id=Pics.dat_ID AND PixMax.MaxDate=pics.DateUpdated) INNER JOIN Cats ON pics.cat_id=cats.id