Get a list of Top 10 authors in a WordPress blog, and sort them by firstname and last name

Getting top 10 authors’s list from WordPress is actually pretty simple with a SELECT query. Here is the SQL for that which sorts the list by number of posts of each authors

SELECT wp_users.id,
       display_name,
       user_login,
       user_nicename,
       count(wp_posts.id) AS number_of_posts
FROM   wp_users
       JOIN wp_posts
         ON wp_posts.post_author = wp_users.id
GROUP  BY wp_posts.post_author
ORDER  BY number_of_posts DESC
LIMIT  10 

And sorting this list by first name is pretty simple as well

SELECT *
FROM   (SELECT wp_users.id,
               display_name,
               user_login,
               user_nicename,
               count(wp_posts.id) AS number_of_posts
        FROM   wp_users
               JOIN wp_posts
                 ON wp_posts.post_author = wp_users.id
        GROUP  BY wp_posts.post_author
        ORDER  BY number_of_posts DESC
        LIMIT  10) AS SortedByCount
ORDER  BY display_name

However, sorting that list by Lastname is a little tricky, we need to take extra care for display_name and extract the last name from it.

SELECT *
FROM   (SELECT wp_users.id,
               display_name,
               user_login,
               user_nicename,
               count(wp_posts.id) AS number_of_posts,
               substring_index(display_name, ' ', -1) AS lastname
        FROM   wp_users
               JOIN wp_posts
                 ON wp_posts.post_author = wp_users.id
        GROUP  BY wp_posts.post_author
        ORDER  BY number_of_posts DESC
        LIMIT  10) AS SortedByCount
ORDER  BY lastname

Now all you need to do is run it like this

$top_authors =  $wpdb->get_results($sql, ARRAY_A);

Sweet, eh?