Welcome to WebmasterWorld Guest from 54.211.17.91

Forum Moderators: open

Message Too Old, No Replies

MySQL - Group By Problem

   
2:29 pm on Sep 13, 2010 (gmt 0)



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.
3:36 pm on Sep 13, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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
4:58 pm on Sep 13, 2010 (gmt 0)



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.
5:13 pm on Sep 13, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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.
5:23 pm on Sep 13, 2010 (gmt 0)



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
5:29 pm on Sep 13, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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]

5:32 pm on Sep 13, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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).
5:34 pm on Sep 13, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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.
5:49 pm on Sep 13, 2010 (gmt 0)



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
5:56 pm on Sep 13, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



Where is pid coming from? That's not in the query I gave you.
5:59 pm on Sep 13, 2010 (gmt 0)



what you gave me works but when I try and get a an id for that picture (pid) it breaks.
6:04 pm on Sep 13, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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?
6:14 pm on Sep 13, 2010 (gmt 0)



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'
6:15 pm on Sep 13, 2010 (gmt 0)



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month