Is there a way or make a group using mysql?

Author Name Status Posted Time Cindrella Schreiber 10/29/2008 3:40:39 AM Author is Offline Hi, I've just about finished setting up a site but if you go to the comments section, it shows the first comment rather than the latest when there's more than one comment for the pic. Basically i've got a table with imageid, commentid, commenttext, etc in, I use the GROUP BY imageid to only show one comment for each unique image, and then ORDER BY commentid, so the latest comments are at the top. However, GROUP BY shows the first comment rather t han the latest - so my question is, is there a way or make the GROUP BY show the latest comment rath er than the first? I'm currently using this: SELECT * FROM `commentstable` GROUP BY `imageid` ORDER BY `commentid` DESC And example data:   Code: commentid      imageid     commenttext 1                5         blah blah this is my first comment 2                6         this is my first comment on a different image 3                5         this is my second comment for the first imageAnd instead of showing commentid 3 when grouped, it shows commentid 1 Anyone follow me as I'm getting confused myself!? If so, any suggestions? Thanks in advance! Cindrella

Subject Author Status Date Re: Is there a way or make a group using mysql? Vishal Gajrajia 10/30/2008 3:38:46 AM Edit! Sorry, stupid suggestion. Post Comments Re: Thanks for the quick reply Cindrella Schreiber 10/30/2008 3:39:40 AM Thanks for the quick reply Vishal- I tried that but unfortunately that didn't help either Post Comments Re: Is there a way or make a group using mysql? Ambrosio Alvalancci 10/30/2008 3:41:28 AM Cindrella, i don't think you can do what you want to do using a simple sql command. also, the group by clause is not supposed to be used for that purpose - you would typically use it to facilitate a function like count(*)... what you can do is to select your records ordered by imageid and then by commentid. then using a loop, loop through the resultset and only pick out the first record for each unique imageid... Post Comments Re: Thanks for all the help! Cindrella Schreiber 10/30/2008 3:42:25 AM That's done the trick - thanks! Was only looking at it from a mysql point of view, didn't even think about looping it in php. Thanks for all the help! Post Comments Re: Sorting in PHP is quite a bit less effective Emily Safford 10/30/2008 3:44:54 AM Sorting in PHP is quite a bit less effective than using SQL, especially with large data sets. Remove the quotes from your SQL: SELECT * FROM commentstable GROUP BY imageid ORDER BY commentid DESC Post Comments

Related Forums Optimizing Images Get a Website Template for Free!!! Get a Website Template for Free!!! Do You Sell Your Templates on Ebay? Template Tutorials