Query to Get Each Category and it's latest Post

Hey guys, having a bit of a problem all day with this one hopefully someone can help.

Basically I have two tables. One called gallery_category and another called gallery_images

What I want to do is bring back each category with it's latest post.

This is what I have at the moment:

			SELECT *
			FROM `{PF}gallery_category` 
			LEFT JOIN (SELECT *
						FROM  `gallery_images` 
				  ) v
			ON `category_id` = `image_category`

Code (markup):

The problem I am having is it brings back each row with it's category. So I have the results:

category_id	category_parent	category_name	category_description	image_id	image_name	image_path	image_thumbnail	image_original	image_category	image_date
1	0	Example Category One	This is an example category for testing the new ga...	2	Random	2.jpg	2.thumb.jpg	http://i.ehow.com/images/a04/d2/0u/random-acts-kin...	1	2010-07-03 13:26:18
1	0	Example Category One	This is an example category for testing the new ga...	1	Morer	1.jpg	1.thumb.jpg	http://i.ehow.com/images/a04/d2/0u/random-acts-kin...	1	2010-07-03 13:26:49
2	0	Example Category Two	This is another example category for testing the n...	NULL	NULL	NULL	NULL	NULL	NULL	NULL
3	1	Test Subcategory	This is a test sub category	3	Another	3.jpg	3.thumb.jpg	http://i.ehow.com/images/a04/d2/0u/random-acts-kin...	3	2010-07-03 15:57:22

Code (markup):

So it's bringing back each category twice. I am wanting it to just bring back each category once, with the most recent image in a left join.

Thanks for any help guys.

 

What may make more sense actually is:

From my list of images, how can I bring back the most recent (image_date) image for each category.


I think I have worked out:

			SELECT *
			FROM  `gallery_category`
			LEFT JOIN (    SELECT * FROM `gallery_images` ORDER BY `image_date` DESC
			) v
			ON image_category = category_id
			WHERE `category_parent` = ''
			GROUP BY category_id

Code (markup):

But the only problem is I'm worried this is quite a long query and with a large database it might take quite a while to process. Could this be simplified!

 

Try this statement:

where image_id is the field which MAX value you want.

 

georgiivanov, thanks very much for getting back to me on this. It' is much appreciated.

You have solved it. Or at pretty much did. I am now using this query:

SELECT gc.category_id, MAX(gi.image_date), image_link
FROM `gallery_category` gc, `gallery_images` gi
WHERE gc.category_id = gi.image_category
GROUP BY gc.category_id

Code (markup):

It does the same as you're only orders it my the MAX date, and you have taught me how to use the MAX and GROUP BY effectivly. Something I probably should have learnt a long time ago

Thanks mate!