homepage Welcome to WebmasterWorld Guest from 54.211.219.68
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
MySQL - Group By Problem
melar09



 
Msg#: 4201161 posted 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.

 

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4201161 posted 3:36 pm on Sep 13, 2010 (gmt 0)

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



 
Msg#: 4201161 posted 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.

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4201161 posted 5:13 pm on Sep 13, 2010 (gmt 0)

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



 
Msg#: 4201161 posted 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

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4201161 posted 5:29 pm on Sep 13, 2010 (gmt 0)

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

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4201161 posted 5:32 pm on Sep 13, 2010 (gmt 0)

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

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4201161 posted 5:34 pm on Sep 13, 2010 (gmt 0)

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



 
Msg#: 4201161 posted 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

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4201161 posted 5:56 pm on Sep 13, 2010 (gmt 0)

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

melar09



 
Msg#: 4201161 posted 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.

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4201161 posted 6:04 pm on Sep 13, 2010 (gmt 0)

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



 
Msg#: 4201161 posted 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'

melar09



 
Msg#: 4201161 posted 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved