I'm trying to write a MySQL query for a database that displays pictures. Each picture is in a category and the record includes the date that the record was added to the database. In addition to having links which display the categories, I'd like to have one that displays the most recent image in each category. That way, people can see if there's anything new since the last time they looked at the whole category. (I'm also ordering the category by date, descending, so the most recent image is displayed first.)

I can't seem to figure out the syntax for the query to find the most recent in each category, though. I could do a query for each category, but that seems excessive.


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.

Hi,

Do you have an example MySQL dump of the 2 related tables?

Maybe try using MAX(ID) or similar? and then try to grab the max categoryID for that ID?

Hope that helps (not a lot I can suggest without more details - is this for DBMan SQL, LSQL, or a custom script?)

Cheers

Andy (mod)
andy@ultranerds.co.uk

---

Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!

I'd forgotten all about this. :-)

I decided to go about this a different way, though, and have a random image from the category display on the front page, rather than the most recent one. I have a thing for randomness. :-D Besides, it's not that much of a hardship to look at pretty pictures to see if there's something new. :-)

Still, to answer your questions, this is a custom script. The fields for the table are

gallery_id (autoincrement)
gallery_cat_id (related to another table that has the names and hierarchy of the categories)
title
description
file (name of the image)
date (in Unix format)


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.