Geek Answers Handbook

MySQL order messages by last comment OR last message

How can I sort messages so that the 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 messages recently, where the new post is obviously later than the previous one, but the old post with the last comment associated with it qualifies as even more recent.

First try

 # 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 does not work because new posts are sorted after old posts with comments.

Second 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

It does not work, because if the message has more than one comment, the regent will have the created value of the first line, which is the oldest comment.

* Is there a way to group by p.id (so only one copy of each message is selected), but sorting within each group is done using c.date desc, but the order of the queries is done using repetition? I can’t figure out how to do this without adding an updated field for the publication, which I will write whenever the response is sent ...

Thanks!

+2
source share
1 answer

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

Assuming the 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
+4
source

More articles:


All Articles