Bug #71942 Select * -- does it have to be "indeterminate" when using ORDER BY and GROUP BY

• Submitted: 5 Mar 2014 15:21
• Modified: 5 Mar 2014 15:53
• Reporter: John Pratt
• Status: Not a Bug
• Category: MySQL Server
• Severity: S5 (Performance)
• OS: Any
• CPU Architecture: Any
• Tags: GROUP BY

[5 Mar 2014 15:21] John Pratt

Description:
http://dev.mysql.com/doc/refman/5.6/en/example-maximum-column-group-row.html#c12849

Please see the last few comments. Basically: it would be nice to be able to rely on a query such as this

SELECT * FROM table
GROUP BY id
ORDER BY price

to get the entire row of the max price per id. Is that possible?

How to repeat:
Use "order by" with group by to attempt to get the entire row of a max value

[5 Mar 2014 15:53] MySQL Verification Team

Hello,

All SQL standards, from SQL:92 until SQL:2011 prescribe that all elements of the SELECT list must be either the aggregating functions or fields in the GROUP BY clause. All other fields in the SELECT list would have totally undetermined values. Most of other RDBMS will not give you undetermined values, but something worse. No values would be returned for the query as the one you provided, but  you would just get an error. If you want to know more about this, read the extension T301 in SQL:2011 standard.