sql - MySQL order posts by most recent comment OR last posted

Question

How can I sort posts so most recent activity is on top?

# Schema not including all info, including FKs
CREATE TABLE post(
   id int unsigned primary key auto_increment,
   msg text,
   created datetime
)ENGINE=InnoDb;

CREATE TABLE comment(
   id int unsigned primary key auto_increment,
   post_id int unsigned,
   msg text,
   created datetime
)ENGINE=InnoDb;

I want to order posts by most recent, where a new post is obviously more recent than one previously posted, but an old post that has a recent comment associated with it qualifies as more recent still.

1st attempt

# Selecting '*' for simplicity in this example
select *
from post p
left join comment c on c.post_id = p.id
group by p.id
order by c.created desc, p.created desc

This doesn't work because new posts are sorted after old posts with comments.

2nd attempt

select *, if(c.id is null, p.created, c.created) as recency
from post p
left join comment c on c.post_id = p.id
group by p.id
order by recency desc

Doesn't work because if a post has more than one comment, recency will have the created value of the first row matched, which is the oldest comment.

*Is there a way to group by p.id (so only one copy of each post is selected), but the sorting within each group is by c.date desc, but the query order by is done by recency? I can't think of a way to do that without possibly adding a updated field to post, which I'd write to whenever a reply is posted...

Thanks!

Answer 1

This should do it:

SELECT p.id
FROM post p
    LEFT JOIN comment c on c.post_id = p.id
GROUP BY p.id
ORDER BY COALESCE(GREATEST(p.created, MAX(c.created)), p.created) DESC

If we assume that a comment is always older than the post, we can simplify:

SELECT p.id
FROM post p
    LEFT JOIN comment c on c.post_id = p.id
GROUP BY p.id
ORDER BY COALESCE(MAX(c.created), p.created) DESC