Wordpress custom sql query

Question

I want to display all authors by their recent published post date. With the code below, I can easily list the authors. But I want to display each author one time. I made a research and found DISTINCT statement but couldnt make it. Could you please help me about this?

Thank you

    global $wpdb;
    $querystr = "SELECT * from wp_users, wp_posts WHERE wp_users.ID = wp_posts.post_author AND wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish' ORDER BY wp_posts.post_date DESC LIMIT 5";
      $news=$wpdb->get_results($querystr, OBJECT);
    foreach($news as $np)
          {
            $author_displayName = $np->display_name;
            $post_date= $np->post_date;
}

Answer 1

EDIT for working solution, original below the line: Just found this older solution on SO: MySQL order by before group by

They want to extract the full posts table, but apparently you're only interested in the username and last post date, so this should be a better solution without nesting two queries:

SELECT max(wp_posts.post_date) MaxPostDate, wp_users.display_name
FROM wp_posts
JOIN wp_users ON (wp_posts.post_author = wp_users.id)
WHERE wp_posts.post_status='publish' AND wp_posts.post_type='post'
GROUP BY post_author

ATTN: In MySQL >= 5.7.5 and when ONLY_FULL_GROUP_BY SQL mode is enabled you'll have to surround all columns that are not mentioned in the GROUP BY statement with the ANY_VALUE() function. i.e.:

SELECT max(wp_posts.post_date) MaxPostDate, ANY_VALUE(wp_users.display_name)

---

SELECT wp_users.display_name, wp_posts.post_date
FROM wp_users
LEFT JOIN wp_posts ON (wp_users.ID = wp_posts.post_author)
WHERE wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish' 
GROUP BY wp_users.display_name
ORDER BY wp_posts.post_date DESC
LIMIT 5

This should give you the desired output.

Now to the explanation:

1. SELECT and then list only those fields you really need, as that may make the query faster.
2. only use one from and then JOIN the other table. In this case this is only a matter of preferred syntax and clarity
3. The where clauses are reduced by the one for matching the two tables together
4. using GROUP BY you can select different columns to show from the ones you want to be unique
5. ORDER BY and LIMIT stay just the way they are

The downside of this: GROUPing is done BEFORE ordering. Thus you'll get the results in whatever order their primary keys occur and the ordering by time will only apply to the order in which the results will be displayed, not which times will be shown for each user.