Need help with a sub query/group/order (get latest comment for each ordered topic)
- by Scarface
Hey guys I have a query that currently finds the latest comment for each of a user's topics and then orders topics by that comment's timestamp. What I want to do is expand on this query's use and print the latest comment for each topic. The problem with this query is that while it orders the topics correctly, it prints seemingly random comments for each topic. I am trying to implement a sub query but I am not quite sure how to approach it. I was thinking that I just had to somehow use this query to get the comments. If anyone has any ideas I would really appreciate it.
Here is what I think I need to add
SELECT * FROM comments where topic_id='$topic_id' ORDER BY timestamp DESC LIMIT 1
Here is the query I need to modify
SELECT topic.topic_title, topic.content_type, topic.subject_id, topic.creator, topic.description, topic.topic_id,comments.message,comments.user
FROM comments
JOIN topic ON topic.topic_id = comments.topic_id
WHERE topic.creator = '$user' AND comments.timestamp > $week
GROUP BY topic_id ORDER BY MAX(comments.timestamp) DESC
