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

    Join Date
    Mar 2000
    Posts
    12
    Rep Power
    0
    I've got a table which contains comments to images. The table has an id field (auto number), a field referencing the image the comment is about, the comment its self and the date of the comment. The table is called comments. I want to select the most recent comment for each image_id. But I can't, I've tried all sorts of GROUP BYs on image_id and used MAX(postdate) or MAX(id) but I can't get the right comment out. I've tried selecting MAX(postdate) AS something and then using HAVING something, but the all have this of course, so I don't know. It doesn't help that MySQL doesn't seem to be able to ORDER BY and then GROUP BY a select.

    Can anyone help? There has to be an easy solution to stop me losing sleep...
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Try this.

    select comment,max(postdate) from comments group by image_id;
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Posts
    12
    Rep Power
    0
    That seems to give the latest "postdate", but the first comment. I want to get the comment which corresponds to MAX(postdate), what you suggested was what I tried first - it doesn't seem to be working.
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Hmm, odd.

    Try this then:

    select comment from comments where postdate=max(postdate) group by image_id;
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Posts
    12
    Rep Power
    0
    that's another thing i've tried, it produces a really crappy mySQL error which doesn't help. What I really need is a sub-select, its the only way I can join against MAX(postdate), as far as I can see. I was playing at work today, trying to do it in Access (out of desperation) and that has more functions which can be used with GROUP BY, including LAST(), which does what I need it do, i.e. if I do LAST(comment) it gives it to me. mySQL doesn't seem to have anything like this... I know Access can never out-do mySQL - there must be solution, somewhere.
  10. #6
  11. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Perhaps if you posted the "crappy mysql error"....

    details, details, details if you want help
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Posts
    12
    Rep Power
    0
    the message is

    MySQL said: Invalid use of group function

    not very useful, is it?
  14. #8
  15. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Yeah, I had my doubts about whether that would work or not.

    That brings us back to the first query. That SHOULD work. I tried it myself and it worked fine... of course, I had to guess at your data structure. If you'd like, email me a schema/data dump of the table and I'll take a look.

Similar Threads

  1. extract group with group by
    By dbosz in forum PHP Development
    Replies: 1
    Last Post: February 4th, 2004, 10:21 PM
  2. mixing group colums with no group coloms
    By Hall in forum MySQL Help
    Replies: 4
    Last Post: February 1st, 2004, 02:06 PM
  3. need help in left join and group in select
    By erin2004 in forum MySQL Help
    Replies: 0
    Last Post: January 20th, 2004, 10:41 AM
  4. strange mysql/innodb group by results
    By hsitz in forum MySQL Help
    Replies: 2
    Last Post: July 17th, 2003, 10:07 PM
  5. I am willing to support a PHP group.
    By micro in forum PHP Development
    Replies: 6
    Last Post: September 10th, 2001, 06:51 PM

IMN logo majestic logo threadwatch logo seochat tools logo