MySQL Order before Group by

Question

I need to find the latest post for each author and then group the results so I only a single latest post for each author.

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

This is correctly grouping the output so I only get one post per author, but it is ordering the results after they have been grouped and not before they have been selected.

Answer 1

it doesn't matter if you order before or after the group-statement, because order means only that 213 goes to 123 or 321 and not more. group by takes only SOME entry per column, not only the latest. I consider you working with subselects here like

SELECT wp_posts.* FROM wp_posts
        WHERE wp_posts.post_status='publish'
        AND wp_posts.post_type='post'
        AND wp_posts.post_date = (Select max(post_date) from wp_posts where author = ... )

Answer 2

What do you think about this?? Seems to work for me

SELECT wp_posts.post_author, MAX(wp_posts.post_date), wp_posts.status, wp_posts.post_type
FROM wp_posts
    WHERE wp_posts.post_status='publish'
    AND wp_posts.post_type='post'
    GROUP BY wp_posts.post_author

It brings me all the Authors with the most updated post_date ... Do you identify a problem there?? I don't

Answer 3

Do a GROUP BY after the ORDER BY by wrapping your query with the GROUP BY like this:

SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t GROUP BY t.author

Answer 4

When our table became large, performance need to checked also. I checked all the options in the questions here, with a PM system with a 136K messages and link table with 83K rows.

When you need only count, or only IDs - Alex's solution is the best.

SELECT wp_posts.post_author, MAX(wp_posts.post_date), wp_posts.status, wp_posts.post_type
FROM wp_posts
    WHERE wp_posts.post_status='publish'
    AND wp_posts.post_type='post'
    GROUP BY wp_posts.post_author

When you need other fields, I need to modify Husky110 solution (to my table design - here it is only example - not checked), that in my tables 10x faster than the subquery option:

SELECT wp_posts.* FROM wp_posts,
    (Select post_id as pid,  max(post_date) maxdate from wp_posts where author = ... group by author order by maxdate  desc limit 4) t
    WHERE wp_posts.post_status='publish'
    AND wp_posts.post_type='post'
    AND wp_posts.post_id = pid

This change can select more than one post (one for user, for example), and can be modified to other solutions.

Moshe.

Answer 5

I think that @edze response is wrong.

In the MySQL manual you can read:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.

Two great references:

• http://kristiannielsen.livejournal.com/6745.html
• http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Sorry, but I can not comment the @edze response because of my reputation, so I have written a new answer.

Answer 6

    SELECT wp_posts.*,max(wp_posts.post_date) FROM wp_posts
    WHERE wp_posts.post_status='publish'
    AND wp_posts.post_type='post'
    GROUP BY wp_posts.post_author