#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Posts
    12
    Rep Power
    0
    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?
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Location
    Southern California
    Posts
    73
    Rep Power
    15
    <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? :-)
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Posts
    12
    Rep Power
    0
    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.

Similar Threads

  1. Mysql problem.
    By Nik in forum MySQL Help
    Replies: 54
    Last Post: December 21st, 2006, 07:02 PM
  2. MySQL password problems NOT root
    By braveheart in forum MySQL Help
    Replies: 2
    Last Post: February 15th, 2004, 09:18 AM
  3. problems with unicode (UTF8) and MySQL 4.1
    By xdummy in forum MySQL Help
    Replies: 0
    Last Post: January 28th, 2004, 05:21 AM
  4. MySQL Control Center connection problems
    By FragMaster B in forum MySQL Help
    Replies: 3
    Last Post: January 19th, 2004, 10:44 AM
  5. Replies: 15
    Last Post: January 8th, 2004, 06:54 PM

IMN logo majestic logo threadwatch logo seochat tools logo