StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>A simple solution is to wrap the query into a subselect with the ORDER statement <em>first</em> and applying the GROUP BY <em>later</em>:</p> <pre><code>SELECT * FROM ( SELECT `timestamp`, `fromEmail`, `subject` FROM `incomingEmails` ORDER BY `timestamp` DESC ) AS tmp_table GROUP BY LOWER(`fromEmail`) </code></pre> <p>This is similar to using the join but looks much nicer. </p> <p>Using non-aggregate columns in a SELECT with a GROUP BY clause is non-standard. MySQL will generally return the values of the first row it finds and discard the rest. Any ORDER BY clauses will only apply to the returned column value, not to the discarded ones. </p> <p><strong>IMPORTANT UPDATE</strong> Selecting non-aggregate columns used to work in practice but should not be relied upon. Per the <a href="https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html" rel="noreferrer">MySQL documentation</a> "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 <strong>free to choose any value</strong> from each group, so <strong>unless they are the same, the values chosen are indeterminate</strong>." </p> <p>As of 5.6.21 I have noticed issues with the GROUP BY on the temporary table reverting the ORDER BY sorting. </p> <p>As of <a href="https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html" rel="noreferrer">5.7.5</a> ONLY_FULL_GROUP_BY is enabled by default, i.e. it's impossible to use non-aggregate columns. </p> <p>See <a href="http://www.cafewebmaster.com/mysql-order-sort-group" rel="noreferrer">http://www.cafewebmaster.com/mysql-order-sort-group</a> <a href="https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html" rel="noreferrer">https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html</a> <a href="https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html" rel="noreferrer">https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html</a></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