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
I tried to get what you posted working however
SELECT pic_id pic_cat, MIN(pic_timestamp) as pic_timestamp FROM pictures GROUP BY pcid
Still only returns the very first record in the database not the first one.
Because of "GROUP BY pcid" (I assume you meant pic_id?).
You need to use "GROUP BY pic_cat" to get the latest date for each pic_cat.
Yeah sorry I forgot to change the value it is pic_cat pcid is the actual column name for cat id saved with the pics. I have also been experimenting with outer/inner joins to no avail.
And this also doesn't work as I have tried it.
SELECT DISTINCT(pic_cat), pic_id, pic_timestamp
ORDER BY pic_timestamp DESC
It returns a result of all the rows ordered by the timestamp
I'm getting confused- maybe you could list the actual field names. And re-type your query- make sure there are no typos.
(Says the person who wrote "Pics.dat_ID" in his example...)
[edited by: LifeinAsia at 5:32 pm (utc) on Sep 13, 2010]
|And this also doesn't work as I have tried it. |
No, it doesn't look like it should work at all- it doesn't make any logical sense (in SQL logic).
If I understand correctly, you should be using:
SELECT pic_cat, MAX(pic_timestamp)
GROUP BY pic_cat
What does that give you?
Note- MIN would give you the oldest date, not the most recent date.
That works it gives me the most recent timestamp for each cat. However when you try and get a id for a picture it breaks
pcid | MAX(ptstamp) | pid
1 | 1275840824 | 1
2 | 1274457367 | 23
6 | 1284141636 | 28
7 | 1230186685 | 34
8 | 1189904260 | 39
The pstamp(Timestamp) is correct and the most recent however the pid(Individual Picture ID) is incorrect and is the id of the very first picture added for each cat.
The correct timestamp for pid #1 = 1183931981
Where is pid coming from? That's not in the query I gave you.
what you gave me works but when I try and get a an id for that picture (pid) it breaks.
|when I try and get a an id for that picture (pid) it breaks |
How are you trying to do that? Did you take the query that's working and modify it like the original example I provided?
Okay reworked your query plus what we just worked on and got this mySQL
SELECT cats.*, pics.* FROM (SELECT pcid, MAX(ptstamp) FROM pictures GROUP BY pcid) AS pixmax INNER JOIN pictures pics ON (pixmax.ptstamp=pics.ptstamp) INNER JOIN pictures_cats cats ON(pixmax.pcid=cats.cid)
and get the following back:
SELECT cats . * , pics . *
SELECT pcid, MAX( ptstamp )
GROUP BY pcid
) AS pixmax
INNER JOIN pictures pics ON ( pixmax.ptstamp = pics.ptstamp )
INNER JOIN pictures_cats cats ON ( pixmax.pcid = cats.cid )
#1054 - Unknown column 'pixmax.ptstamp' in 'on clause'
Nevermind I got it had to change in the subquery MAX( ptstamp ) to MAX(ptstamp) AS ptstamp.
Thanks for your help and it worked perfectly.