MySQL Select Group by show the latest post

1 Views
$postids = $wpdb->get_results( 
    "
    SELECT ID, post_title, post_author, max(post_date)
    FROM $wpdb->posts
    WHERE post_status = 'publish' 
          AND post_type = 'post'
    GROUP BY post_author
    ORDER BY post_date DESC
    "
);

foreach ( $postids as $postid ) 
{
    echo $postid->ID." :: ".$postid->post_title." :: ".$postid->post_author . "<br />" ;
}

I want to select one latest post from each author but the above code still select the oldest post from each author. max(post_date) does not seem to be working. any advice to make this work?

Post Views: 1

Related posts

1 comment

  1. You first need to to discover the date of each author’s most recent post.

             SELECT max(post_date) post_date, post_author
           FROM $wpdb->posts
          WHERE post_status = 'publish' 
           AND post_type = 'post'
         GROUP BY post_author

    You then need to use this result to extract the detail of the posts you want.

    SELECT a.ID, a.post_title, a.post_author, a.post_date
 FROM $wpdb->posts a
 JOIN (
         SELECT max(post_date) post_date, post_author
           FROM $wpdb->posts
          WHERE post_status = 'publish' 
           AND post_type = 'post'
         GROUP BY post_author
      ) b ON a.post_author = b.post_author AND a.post_date = b.post_date
 WHERE post_status = 'publish' 
   AND post_type = 'post'
 ORDER BY post_date DESC

    GROUP BY is a little tricky when you’re working on this purpose.

    Note: I haven’t debugged this query in the WordPress environment.

    Notice that if an author manages to create two posts at the same moment, they will both have post_date values that match the MAX(post_date) value. In that case this query returns them both.

Comments are closed.