August 27th, 2000, 05:34 AM
I'm trying to get the GROUP BY function to show the rows I want. I'm grouping by an "id", which is sort one-to-many in a table which is left joined to but I want to be able to show the row which was last inserted into the table (it will have the MAX(timestamp) effectively)... but at the moment its showing the first one... maybe not explained that very well... what I've got is a list of images in a table called "images", with fields "id" and "filename", I then have a second table called "comments" with fields "id", "image_id", "postdate", "user_id" and "caption". Basically I want to display all the images with their most recent captions, only they wont all have captions in the second table. At the moment my SQL goes something like "SELECT * FROM images LEFT JOIN comments ON images.id = comments.image_id GROUP BY images.id". This outputs the first "caption". I've tried playing with ORDER BY (its doesn't seem to like the GROUP BY) and MAX(postdate)... but so far nothing works. Can anyone please help?
August 29th, 2000, 12:39 PM
<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by leaed:
I've tried playing with ORDER BY (its doesn't seem to like the GROUP BY) and MAX(postdate)... but so far nothing works. Can anyone please help?[/quote]
Why not do something like this:
SELECT images.*, comments.caption, MAX(comments.postdate) AS current FROM images,comments WHERE images.id = comments.image_id GROUP BY images.id HAVING current
Or am I misunderstanding your post? :-)
August 31st, 2000, 03:51 AM
That doesn't quite solve it. The problem is that there isn't a post date for all of the images in the 'images' table, so using HAVING MAX(postdate) (which is kinda what you said, but I did try the suggested way) only selects the ones that have a 'postdate'. I need to get all of the images from 'images' and their most recent 'caption', by postdate, where there is a caption to get.
It seems like there should be a fairly straight forward solution, but I can't see it.