MySQL - Group By Problem

[melar09]

Okay I have two database tables, one called pictures and another called picture cats. What I want to do is fetch the most recent picture for each cat along with the cat information so it would look like this...

Picture Gallery
- Some Cat
[Newest Pic for some cat]
- Another Cat
[Newest Pic for this cat]
and so on...


Here is what I got so far...

SELECT cats.*, pics.* FROM cats JOIN pics ON (pics.cat_id=cats.id) GROUP BY cats.id ORDER BY cats.timestamp, pics.timestamp DESC

But it returns the list of cats with the first added picture for each cat. I have searched for a bit and have tried a few different things I have a feeling I have to do a sub query but I am not very good with them yet. That is also not my exact query as far as table names etc but its setup like that.

[LifeinAsia]

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
FROM pics
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

[melar09]

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.

[LifeinAsia]

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.

[melar09]

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
FROM pictures
ORDER BY pic_timestamp DESC

It returns a result of all the rows ordered by the timestamp

[LifeinAsia]

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]

[LifeinAsia]

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).

[LifeinAsia]

If I understand correctly, you should be using:
SELECT pic_cat, MAX(pic_timestamp)
FROM pictures
GROUP BY pic_cat

What does that give you?

Note- MIN would give you the oldest date, not the most recent date.

[melar09]

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

[LifeinAsia]

Where is pid coming from? That's not in the query I gave you.

[melar09]

what you gave me works but when I try and get a an id for that picture (pid) it breaks.

[LifeinAsia]

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?

[melar09]

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 . * 
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 ) 

MySQL said: 

#1054 - Unknown column 'pixmax.ptstamp' in 'on clause' 

[melar09]

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.