StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Using an <code>ORDER BY</code> in a subquery is not the best solution to this problem. </p> <p>The best solution to get the <code>max(post_date)</code> by author is to use a subquery to return the max date and then join that to your table on both the <code>post_author</code> and the max date.</p> <p>The solution should be:</p> <pre><code>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 </code></pre> <p>If you have the following sample data:</p> <pre><code>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') ; </code></pre> <p>The subquery is going to return the max date and author of:</p> <pre><code>MaxPostDate | Author 2/1/2013 | Jim </code></pre> <p>Then since you are joining that back to the table, on both values you will return the full details of that post.</p> <p>See <a href="http://www.sqlfiddle.com/#!2/b0645/2" rel="noreferrer">SQL Fiddle with Demo</a>.</p> <p>To expand on my comments about using a subquery to accurate return this data. </p> <p>MySQL does not force you to <code>GROUP BY</code> every column that you include in the <code>SELECT</code> list. As a result, if you only <code>GROUP BY</code> one column but return 10 columns in total, there is no guarantee that the other column values which belong to the <code>post_author</code> that is returned. If the column is not in a <code>GROUP BY</code> MySQL chooses what value should be returned.</p> <p>Using the subquery with the aggregate function will guarantee that the correct author and post is returned every time. </p> <p>As a side note, while MySQL allows you to use an <code>ORDER BY</code> in a subquery and allows you to apply a <code>GROUP BY</code> to not every column in the <code>SELECT</code> list this behavior is not allowed in other databases including SQL Server.</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