I am attempting to order Wordpress posts by the newest comment. To the very best of my understanding this is not possible while using Wordpress_Query object, and would need a custom $wpdb query, that we can certainly write. However, Then i don't understand how to setup the loop to operate off this object.

Can anybody help?

Code recommended by Hao Lian works perfect except the truth that we ought to add the next WHERE clasue to prevent tugging Publish with comment_count = , this case is triggered by junk e-mail comments.

The WHERE clause to include is the following:

WHERE comment_approved = '1' AND comment_type = '' AND publish_password = ''

Complete code after adding the where clause shoud seem like following:

choose wordpress_posts.*, max(comment_date) as comment_date from wordpress_posts right join wordpress_comments on id = comment_publish_id WHERE comment_approved = '1' AND comment_type = '' AND publish_password = '' group by ID
order by comment_date desc limit 6

Its really quite simple and merely just one type of code change is going to do that. I authored a post a while back relating to this. Book if you're interested.

Edit: I believe I acquired your question wrong :). I figured you need to display the newest comment inside a publish. Sorry about this.

I am convinced that including the max function will ruin your results. MySQL is not likely to pull the max from each one of these. It is going to pull the max in the full set. This is actually the query that'll enable you to get your results:

select wp_posts.*, comment_date
from $wpdb->posts wp_posts
right join $wpdb->comments
on id = comment_post_id
group by ID
order by comment_date desc
limit 10

Next, if you wish to follow Wordpress convention, make use of this, and you may use the functions that much of your templates are utilizing (in line with the loop):

$results = $wpdb->get_results($query) or die('!');
     foreach ($results as $post):
    setup_postdata($post);

OK men,

Lots of great solutions here, but clearly no one's taken time to check them.

Hao Lian will get the loan for that first best original answer, but regrettably his code does not show posts without comments.

Captain Keytar is on course, but his code displays each and every publish and attachment like a separate result.

This is a modified version of Captain Keytar however it limits the outcomes towards the type 'post'.. that's been released (to prevent getting drafts!!)

    select wp_posts.*,
    coalesce(
        (
            select max(comment_date)
            from $wpdb->comments wpc
            where wpc.comment_post_id = wp_posts.id
        ),
        wp_posts.post_date
    ) as mcomment_date
    from $wpdb->posts wp_posts
    where post_type = 'post'
    and post_status = 'publish' 
    order by mcomment_date desc
    limit 10

Assign

select wp_posts.*, max(comment_date) as comment_date
from $wpdb->posts wp_posts
right join $wpdb->comments
on id = comment_post_id
group by ID
order by comment_date desc
limit 10

with a variable $query. You are able to fiddle around using the 10 or even the query itself. (I am no SQL optimisation ninja.) Your code will appear something similar to

<?php
     $results = $wpdb->get_results($query) or die('!');
     foreach ($results as $result):
?>
[insert template here]
<?php endforeach ?>

This pattern is included in additional depth by the Codex.

Being an addendum to Hao Lian's answer, if you are using the next query:

select wp_posts.*,
coalesce(
    (
        select max(comment_date)
        from $wpdb->comments wpc
        where wpc.comment_post_id = wp_posts.id
    ),
    wp_posts.post_date
) as mcomment_date
from $wpdb->posts wp_posts
order by mcomment_date desc
limit 10

This mixes in posts that do not have comments yet, and sorts them by publish_date and max(comment_date).