Ordering data using mysql

[aleco]

I've just about finished setting up http://mms.mob453.com/ - but if you go to the comments section (http://mms.mob453.com/latestcomments.php), 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 than the latest - so my question is, is there a way or make the GROUP BY show the latest comment rather than the first?

I'm currently using this:
SELECT * FROM `commentstable` GROUP BY `imageid` ORDER BY `commentid` DESC

And example data:


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 image
And 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!

[nullbit]

Edit: Sorry, stupid suggestion.

[aleco]

Thanks for the quick reply - I tried that but unfortunately that didn't help either :(

[daboss]

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...

[aleco]

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 :o

Thanks for all the help! :D

[J.D.]

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 phpSorting 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

J.D.