Need help with a sub query/group/order (get latest comment for each ordered topic)

Question

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

Answer 1

Not sure what your $week var is doing, but I hope it is date formatted.

How about this? I suspect it will be slow, but it is the first thing that came to mind:

SELECT t.topic_title, t.content_type, t.subject_id, t.creator,
   t.description, t.topic_id, c.message, c.user
FROM topic t
INNER JOIN comments c
  ON t.topic_id = c.topic_id
  AND c.comment_id = (select max(c2.comment_id) 
                    from comments c2
                    where c2.topic_id = topic.topic_id)
WHERE t.creator = '$user' 
  AND c.timestamp > $week

Answer 2

This is an example of the greatest-n-per-group problem, which comes up on Stack Overflow frequently. Follow the tag for more examples.

SELECT t.topic_title, t.content_type, t.subject_id, t.creator, t.description, 
  t.topic_id, c1.message, c1.user
FROM topic t
JOIN comments c1 ON (t.topic_id = c1.topic_id)
LEFT OUTER JOIN comments c2
  ON (t.topic_id = c2.topic_id AND c1.timestamp < c2.timestamp)
WHERE t.creator = ? AND c1.timestamp > ?
  AND c2.topic_id IS NULL
ORDER BY c1.timestamp DESC;

PS: Use query parameters (?) for dynamic values, to reduce the risk of SQL injection.

Answer 3

select t.topic_title, t.content_type, t.subject_id, t.creator, t.description, t.topic_id, c.message, c.user
from comments c
inner join (
    SELECT cc.topic_id, max(cc.timestamp) as MaxTimestamp
    FROM comments cc
    inner JOIN topic t ON t.topic_id = cc.topic_id
    WHERE t.creator = '$user' AND cc.timestamp > $week      
    group by cc.topic_id
) cm on c.topic_id = cm.topic_id and c.timestamp = cm.MaxTimestamp
inner JOIN topic t ON t.topic_id = c.topic_id