May 23, 2006 at 6:28 pm
I am trying to display a list of the most recently updated Photo Categories. "Most recently updated" means "Contains the newest photos". But my statement returns a list of the most recent 14 photos, all in the same category. It *should* return a list of 5 categories ordered by the date of the last photo in that category.
select distinct top 5 pc.categoryid,p.datecreated from photos_categories pc inner join photos p on pc.categoryid=p.categoryid order by p.datecreated desc
What it's currently returning:
categoryid datecreated
---------- -----------
104 2006-05-22 01:04:03.280
104 2006-05-22 01:04:03.090
104 2006-05-22 01:04:02.577
104 2006-05-22 01:04:02.357
104 2006-05-22 01:04:02.187
An example of what it *should* be returning:
categoryid datecreated
---------- -----------
104 2006-05-22 01:04:03.280
89 2006-05-21 01:04:03.280
105 2006-05-20 01:04:03.280
4 2006-05-19 01:04:03.280
200 2006-05-18 01:04:03.280
May 23, 2006 at 7:34 pm
I received the answer on another forum (thank you Srinika on SQLTeam.com)
select pc.categoryid, MAX(p.datecreated) AS datecreated from photos_categories pc inner join photos p on pc.categoryid=p.categoryid GROUP BY pc.categoryid order by p.datecreated desc
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply