MySQL Order before Group by

Question

I need to find the latest post for each author and then group the results so I only a single latest post for each author.

SELECT wp_posts.* FROM wp_posts
        WHERE wp_posts.post_status='publish'
        AND wp_posts.post_type='post'
        GROUP BY wp_posts.post_author           
        ORDER BY wp_posts.post_date DESC

This is correctly grouping the output so I only get one post per author, but it is ordering the results after they have been grouped and not before they have been selected.

Answer 1

select wp_posts.* from wp_posts
where wp_posts.post_status='publish'and wp_posts.post_type='post'
group by wp_posts.post_author
having wp_posts.post_date = MAX(wp_posts.post_date) /* ONLY THE LAST POST FOR EACH AUTHOR */
order by wp_posts.post_date desc

---

EDIT:

After some comments I have decided to add some additional informations.

The company I am working at also uses Postgres and especially SQL Server. This databases don't allow such queries. So I know that there is a other way to do this (I write a solution below). You shoud also have to know what you do if you don't group by all columns treated in the projection or use aggregate functions. Otherwise let it be!

I chose the solution above, because it's a specific question. Tom want to get the recent post for each author in a wordpress site. In my mind it is negligible for the analysis if a author do more than one post per second. Wordpress should even forbid it by its spam-double-post detection. I know from personal experience that there is a really significant benefit in performance doing a such dirty group by with MySQL. But if you know what you do, then you can do it! I have such dirty groups in apps where I'm professionally accountable for. Here I have tables with some mio rows which need 5-15s instead of 100++ seconds.

May be useful about some pros and cons: http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/debunking-group-by-myths.html

---

SELECT
    wp_posts.*
FROM 
    wp_posts
    JOIN 
    (
        SELECT
            g.post_author
            MAX(g.post_date) AS post_date
        FROM wp_posts as g
        WHERE
            g.post_status='publish'
            AND g.post_type='post'
        GROUP BY g.post_author
    ) as t 
    ON wp_posts.post_author = t.post_author AND wp_posts.post_date = t.post_date

ORDER BY wp_posts.post_date

But if here is more then one post per second for a author you will get more then one row and not the only last one.

Now you can spin the wheel again and get the post with the highest Id. Even here it is at least not guaranteed that you really get the last one.

Answer 2

Use the below code...

<?php
//get all users, iterate through users, query for one post for the user,
//if there is a post then display the post title, author, content info
$blogusers = get_users_of_blog();
if ($blogusers) {
  foreach ($blogusers as $bloguser) {
    $args = array(
    'author' => $bloguser->user_id,
      'showposts' => 1,
      'caller_get_posts' => 1
    );
    $my_query = new WP_Query($args);
    if( $my_query->have_posts() ) {
      // $user = get_userdata($bloguser->user_id);
      // echo 'This is one post for author with User ID: ' . $user->ID . ' ' . $user-    >user_firstname . ' ' . $user->user_lastname;
      while ($my_query->have_posts()) : $my_query->the_post(); ?>
        <a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?    php the_title_attribute(); ?>"><?php the_title(); ?></a>

        <small><?php the_time('F jS, Y') ?> by <?php the_author_posts_link() ?>     </small><?php
        the_content();
      endwhile;
    }
  }
}
?>

Answer 3

Not sure if I understand your requirement correct but following inner statement gets the list of the latest post_date for each author and joins these back with the wp_posts table to get a complete record.

SELECT  *
FROM    wp_posts wp
        INNER JOIN (
          SELECT  post_author
                  , MAX(post_date) AS post_date
          FROM    wp_posts
          WHERE   post_status = 'publish'
                  AND post_type = 'post'
          GROUP BY
                  post.author
        ) wpmax ON wpmax.post_author = wp.post_author
                   AND wpmax.post_date = wp.post_date
ORDER BY
        wp.post_date DESC

Answer 4

HERE a simple answer from http://www.cafewebmaster.com/mysql-order-sort-group

SELECT * FROM 
(
select * from `my_table` order by timestamp desc
) as my_table_tmp

GROUP BY catid
ORDER BY nid desc

it worked wonders for me