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
[sourcecode language=”sql”]
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
[/sourcecode]
And sorting this list by first name is pretty simple as well
[sourcecode language=”sql”]
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
[/sourcecode]
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.
[sourcecode language=”sql”]
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
[/sourcecode]
Now all you need to do is run it like this
[sourcecode language=”php”]
$top_authors = $wpdb->get_results($sql, ARRAY_A);
[/sourcecode]
Sweet, eh?