>>>> 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
