Databases
StackOverflow2013
Postsdbo
POMySQL order by before group by
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL order by before group by
    text
    copied!<p>There are plenty of similar questions to be found on here but I don't think that any answer the question adequately.</p> <p>I'll continue from the current most popular <a href="https://stackoverflow.com/questions/5140785/mysql-order-before-group-by">question</a> and use their example if that's alright.</p> <p>The task in this instance is to get the latest post for each author in the database.</p> <p>The example query produces unusable results as its not always the latest post that is returned.</p> <pre><code>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 </code></pre> <p>The current accepted answer is </p> <pre><code>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) &lt;- ONLY THE LAST POST FOR EACH AUTHOR ORDER BY wp_posts.post_date DESC </code></pre> <p>Unfortunately this answer is plain and simple wrong and in many cases produces less stable results than the orginal query.</p> <p>My best solution is to use a subquery of the form</p> <pre><code>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 </code></pre> <p>My question is a simple one then: <strong>Is there anyway to order rows before grouping without resorting to a subquery?</strong></p> <p><strong>Edit</strong>: 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.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload