Selecting most recent by Timestamp

[cg5572]

Ok I have a really rookie question which for some reason I can't get my head around:

 

I have a table which stores user comments, with the following fields:

 

- comment_id

- post_id

- user_id

- category_id

- timestamp

 

I'm currently using the following query to return all comments by a user in a given category:

 

SELECT * FROM comments_table WHERE user_id='$user' AND category='$category'

 

What I want to be able to do is retrieve the most recent comment for each post, i.e. if the above query returns multiple comments for the same post_id, show only the most recent one (highest timestamp).

 

Is there a way to build this into the query, or if not what is the best way of extracting them in the PHP afterwards?

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

[cg5572]

Amazing, thanks!

[cg5572]

For anyone who finds this, the final query for this is as follows:

 

SELECT * FROM comments_table s1 WHERE timestamp=(SELECT MAX(s2.timestamp) FROM comments_table s2 WHERE s1.post_id = s2.post_id AND user_id='$user' AND category='$category')