ORDER BYin a subquery is not the best solution to this problem.
The best solution to get the
max(post_date)by author is to use a subquery to return the max date and then join that to your table on both the
post_authorand the max date.
The solution should be:
If you have the following sample data:
SELECT p1.* FROM wp_posts p1 INNER JOIN ( SELECT max(post_date) MaxPostDate, post_author FROM wp_posts WHERE post_status='publish' AND post_type='post' GROUP BY post_author ) p2 ON p1.post_author = p2.post_author AND p1.post_date = p2.MaxPostDate WHERE p1.post_status='publish' AND p1.post_type='post' order by p1.post_date desc
The subquery is going to return the max date and author of:
CREATE TABLE wp_posts (`id` int, `title` varchar(6), `post_date` datetime, `post_author` varchar(3)) ; INSERT INTO wp_posts (`id`, `title`, `post_date`, `post_author`) VALUES (1, 'Title1', '2013-01-01 00:00:00', 'Jim'), (2, 'Title2', '2013-02-01 00:00:00', 'Jim') ;
Then since you are joining that back to the table, on both values you will return the full details of that post.
MaxDate | Author 2/1/2013 | Jim
See SQL Fiddle with Demo.
To expand on my comments about using a subquery to accurate return this data.
MySQL does not force you to
GROUP BYevery column that you include in the
SELECTlist. As a result, if you only
GROUP BYone column but return 10 columns in total, there is no guarantee that the other column values which belong to the
post_authorthat is returned. If the column is not in a
GROUP BYMySQL chooses what value should be returned.
Using the subquery with the aggregate function will guarantee that the correct author and post is returned every time.
As a side note, while MySQL allows you to use an
ORDER BYin a subquery and allows you to apply a
GROUP BYto not every column in the
SELECTlist this behavior is not allowed in other databases including SQL Server.