MySQL order by before group by

by

Using an ORDER BY in 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_author and the max date.

The solution should be:

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

If you have the following sample data:

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')
;

The subquery is going to return the max date and author of:

MaxPostDate | Author
2/1/2013    | Jim

Then since you are joining that back to the table, on both values you will return the full details of that post.

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 BY every column that you include in the SELECT list. As a result, if you only GROUP BY one column but return 10 columns in total, there is no guarantee that the other column values which belong to the post_author that is returned. If the column is not in a GROUP BY MySQL 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 BY in a subquery and allows you to apply a GROUP BY to not every column in the SELECT list this behavior is not allowed in other databases including SQL Server.

More Related Contents:

  1. MySQL “Group By” and “Order By”
  2. Select the 3 most recent records where the values of one column are distinct
  3. MySQL – Control which row is returned by a group by
  4. MySQL Order before Group by
  5. ORDER BY date and time BEFORE GROUP BY name in mysql
  6. MySQL ‘Order By’ – sorting alphanumeric correctly
  7. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  8. MySQL Orderby a number, Nulls last
  9. Grouping into interval of 5 minutes within a time range
  10. How does MySQL process ORDER BY and LIMIT in a query?
  11. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  12. Order a MySQL table by two columns
  13. How to group by week in MySQL?
  14. GROUP_CONCAT ORDER BY
  15. What is MySQL row order for “SELECT * FROM table_name;”?
  16. Slow query when using ORDER BY
  17. Group mysql query by 15 min intervals
  18. MySQL: Sort GROUP_CONCAT values
  19. MySQL order by “best match”
  20. Is it possible to GROUP BY multiple columns using MySQL?
  21. Using Count to find the number of occurrences
  22. Returning query results in predefined order
  23. Mysql delete statement with limit
  24. mysql select sum group by date
  25. Mysql: Order by like?
  26. Sort the rows according to the order specified in WHERE IN clause
  27. mysql count group by having
  28. ORDER BY alphabet first then follow by number
  29. MYSQL Select One Random record from each Category
  30. mysql order by, null first, and DESC after

Leave a Comment