Using SQL MAX to grab latest comments from DB.

[HairyArse]

Hey guys, I'm having some problems with an SQL MAX query I was hoping one of you would be able to help me with.

I'm basically trying to list the 5 most recent comments from my comments table as well as the article they're against and the user id of the poster.

My comments tables is as follows:

comment_id int(7) NOT NULL auto_increment,
user int(6) default NULL,
datestamp timestamp NOT NULL default CURRENT_TIMESTAMP,
article_id int(6) default NULL,
comments blob,
username varchar(25) default NULL,
PRIMARY KEY (comment_id)

(user and username are both stored because I allow anonymous comments - these are assigned a user of -1)

The query I'm trying to get work is as follows:

SELECT a.headline, a.article_id, MAX(b.datestamp) AS last_comment_on, b.username FROM tbl_articles a, tbl_comments b WHERE a.article_id = b.article_id  GROUP BY article_id ORDER BY last_comment_on DESC LIMIT 0,5

I should add that the above query almost works but for some reason it displays the first poster against the article instead of the last.

[Weedpacket]

This is one of the problems with MySQL's nonstandard handling of aggregate functions. Other DBMSs would fail that query because b.username is neither aggregated nor grouped on, leaving it ambiguous. MySQL just picks whatever it feels like.

I think putting a "HAVING b.datestamp=last_comment_on" clause after the GROUP BY clause and before the ORDER BY clause would work.

No, I don't think it will any more. It would probably have to be made another condition in the WHERE clause. Can't say for sure because I don't use MySQL often enough to know how to deal with all its gotchas.