Ordering Wordpress posts by most recently commented on

Question

What's the query to list wordpress posts by most recent, with posts most recently commented on going to the top of the order? (Standard "message board" style)

This post looked promising:

http://stackoverflow.com/questions/698438/ordering-wordpress-posts-by-most-recent-comment

But the query is clearly wrong.

Can anyone help?

Answer 1

How about;

SELECT * FROM $wpdb->posts LEFT JOIN $wpdb->comments
ON $wpdb->posts.ID = $wpdb->comments.comment_post_ID
GROUP BY $wpdb->posts.ID
ORDER BY $wpdb->comments.comment_date DESC
LIMIT X,Y

This would get posts, limited in the range X,Y, with posts that have comments, and those with the most recent comments, listed first.

UPDATED

Additonal ORDER BY clause;

ORDER BY $wpdb->comments.comment_date DESC, $wpdb->post_date DESC