How to Efficiently Get the Latest Post for Each Author in MySQL Without Subqueries?

MySQL Order By Before Group By: An Alternative Approach

Ordering rows before grouping can be challenging in MySQL, particularly when the desired order is not guaranteed by the subsequent group by. While using a subquery is a common solution, it may not always be the most optimal approach.

In the scenario presented, the goal is to retrieve the latest post for each author from the wp_posts table. The provided queries, including the one described as "the current accepted answer," fail to consistently deliver the expected results.

An alternative solution that addresses this issue without resorting to a subquery is to leverage a combination of window functions and a self-join:

WITH LatestPostsPerAuthor AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY post_author ORDER BY post_date DESC) AS rn
    FROM wp_posts
    WHERE post_status='publish'
    AND post_type='post'
)
SELECT a.*
FROM LatestPostsPerAuthor a
JOIN LatestPostsPerAuthor b ON a.rn = b.rn AND a.post_author = b.post_author
GROUP BY a.post_author
ORDER BY a.post_date DESC;

This query employs the ROW_NUMBER() window function to assign a ranking to posts based on their post_date within each author group. The self-join then ensures that only the rows with the highest ranking (i.e., the latest posts) are retained for grouping and ordering.

By using this approach, the desired result can be achieved without the overhead of a subquery, delivering accurate and consistent results for the given scenario.

The above is the detailed content of How to Efficiently Get the Latest Post for Each Author in MySQL Without Subqueries?. For more information, please follow other related articles on the PHP Chinese website!