>>>> 2012/09/10 15:49 -0700, Rick James >>>>
SELECT ... ORDER BY .. GROUP BY..
is syntactically incorrect.
<<<<<<<<
Yeap, my mistake.
>>>>>>>>
( SELECT ... ORDER BY .. ) GROUP BY ..
Is what I call the "group by trick". It is an optimal way to SELECT all the fields
corresponding to the MAX (or MIN) of one of the fields. But it depends on the optimizer
not screwing it up. MariaDB decides that this construct can be optimized, and messes up
the 'trick'.
<<<<<<<<
But this behavior is guaranteed nor in MySQL:
11.16.3. GROUP BY and HAVING with Hidden Columns
MySQL extends the use of GROUP BY so that you can use nonaggregated columns or
calculations in the select list that do not appear in the GROUP BY clause. You can use
this feature to get better performance by avoiding unnecessary column sorting and
grouping. For example, you need not group on customer.name in the following query:
SELECT order.custid, customer.name, MAX(payments)
FROM order,customer
WHERE order.custid = customer.custid
GROUP BY order.custid;
In standard SQL, you would have to add customer.name to the GROUP BY clause. In MySQL, the
name is redundant.
When using this feature, all rows in each group should have the same values for the
columns that are ommitted from the GROUP BY part. The server is free to return any value
from the group, so the results are indeterminate unless all values are the same.
