StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is because, as <a href="http://dev.mysql.com/doc/refman/5.6/en/group-by-hidden-columns.html" rel="nofollow">§11.16.3 "<code>GROUP BY</code> and <code>HAVING</code> with Hidden Columns" in the <em>MySQL 5.6 Reference Manual</em></a> puts it:</p> <blockquote> <p>MySQL extends the use of <code>GROUP BY</code> so that the select list can refer to nonaggregated columns not named in the <code>GROUP BY</code> clause. This means that the preceding query is legal in MySQL. 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 <code>GROUP BY</code> are the same for each group. <strong>The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.</strong> Furthermore, the selection of values from each group cannot be influenced by adding an <code>ORDER BY</code> clause. Sorting of the result set occurs after values have been chosen, and <code>ORDER BY</code> does not affect which values the server chooses.</p> </blockquote> <p>[emphasis mine]</p> <p>Instead, you need to write something like this:</p> <pre><code>select t1a.TelephoneNumber, t1a.Date, t1a.Type from Table1 as t1a left join Table1 as t1b on t1b.TelephoneNumber = t1a.TelephoneNumber and t1b.Date > t1a.Date where t1a.Date > '2012-03-25 00:00' and t1b.TelephoneNumber IS NULL -- i.e., the join failed ; </code></pre> <p>to find the record with the greatest <code>Date</code> for each value of <code>TelephoneNumber</code>.</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