MySQL order by before group by

by

There are plenty of similar questions to be found on here but I don’t think that any answer the question adequately.

I’ll continue from the current most popular question and use their example if that’s alright.

The task in this instance is to get the latest post for each author in the database.

The example query produces unusable results as its not always the latest post that is returned.

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

The current accepted answer is 

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

Unfortunately this answer is plain and simple wrong and in many cases produces less stable results than the orginal query.

My best solution is to use a subquery of the form

SELECT wp_posts.* FROM 
(
    SELECT * 
    FROM wp_posts
    ORDER BY wp_posts.post_date DESC
) AS wp_posts
WHERE wp_posts.post_status="publish"
AND wp_posts.post_type="post"
GROUP BY wp_posts.post_author

My question is a simple one then:
Is there anyway to order rows before grouping without resorting to a subquery?

Edit: This question was a continuation from another question and the specifics of my situation are slightly different. You can (and should) assume that there is also a wp_posts.id that is a unique identifier for that particular post.

12 Answers
12

Leave a Comment