How to get most recent commented post above new submitted post in Wordpress?

Question

I have this function:

$ids = $wpdb->get_col("SELECT DISTINCT comment_post_ID
FROM $wpdb->comments
ORDER BY comment_date DESC
LIMIT 0 , 30");

foreach ($ids as $id) {
  $post = &get_post( $id );
  setup_postdata($post); ?>
  <p><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></p>
  <?php
}
?>

Which shows the latest commented posts in a list, which is fine. What I want to do is give a priority to this one and combine it with a "get newest post list". So let's say I commented today on a post called Hello World and someone else submitted a post yesterday... Than I want to get the recent commented post above this new post. The problem is that in my code snippet, there is nothing that says to get the newest posts. How can I combine them? So how to combine most recent commented posts and newest posts with each other? Is this even possible?

Answer 1

Give it a try works perfect for me what it is doing query get the all the posts with a left jon with comments table so when a post has comment then it also has the comment_date if no comments posted on the post then in result set it will be null so i have merged the comment_date with post_date so which post has the greater date (for comment_date or post_date) it will first and so on

SELECT p.*,
(CASE WHEN c.comment_date IS NULL THEN p.`post_date` ELSE c.comment_date END) order_column
 FROM `wp_posts` p
LEFT  JOIN `wp_comments` c  ON (p.ID = c.`comment_post_ID` ) WHERE p.post_type='post' AND p.post_status='publish'
GROUP BY p.ID
 ORDER BY order_column   DESC

For displaying the posts you have to first get the results by defining the WP's global variable for the database interaction i.e $wpdb

<?php
global $wpdb;
$results = $wpdb->get_results("    SELECT p.ID,
    (CASE WHEN c.comment_date IS NULL THEN p.`post_date` ELSE c.comment_date END) order_column
     FROM `wp_posts` p
    LEFT  JOIN `wp_comments` c  ON (p.ID = c.`comment_post_ID` ) WHERE p.post_type='post' AND p.post_status='publish'
    GROUP BY p.ID
     ORDER BY order_column   DESC LIMIT 20"); 
?>

HTML

<?php
foreach ($results as $id) {
  $post = &get_post( $id->ID );
  setup_postdata($post); ?>
  <p><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></p>
  <?php
} ?>

Hope that is what you were looking for