MySQL Bugs: #12803: selecting a consistent row of aggrigate data during a group

Bug #12803	selecting a consistent row of aggrigate data during a group
Submitted:	25 Aug 2005 13:24	Modified:	17 Jun 2013 14:31
Reporter:	Are you mortal Then prepare to die.	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	next	OS:	Any (all)
Assigned to:	Matthew Lord	CPU Architecture:	Any

Description:

The following syntax comes to mind with reference to the page here...

http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html

How about,

SELECT G1, MAX(ROW(C1,C2,C3)) FROM table GROUP BY G1;

Allowing MAX to work with the ROW opperator would be a natural way to select a consistent row of data based on the maximum value of the C1 column for every distinct G1 value (in this example). Ties handled in a very natural way by selecting the order of the values that appear in the ROW opperator. 

So to select the telephone number of the sales department with the least sales in each region you would say...

SELECT 
  REGION, 
  MIN(ROW(SALES,NAME,NUMBER)) 
FROM 
  sales_region_manager_table
GROUP BY
  REGION;

Unlucky for those managers in the same region with equal sales but names which occur earlier in the alphabet!

Please implement this syntax / lobby the standards commitie / send me money / etc. / etc. 

How to repeat:
Look in the mirror

Suggested fix:
Right away!

Fix SQL!

Thank you for the feature request, and for your help in making MySQL even better!

I'm closing this SR for now, as the ROW method wouldn't produce a consistent row any more than the subquery method would, which should now be performant in 5.6 and later:
  SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

If you would simply like the ROW() function added to MySQL, that is similar to the Oracle function, then please let me know and I can open a new Feature Request for that specifically:
  http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2072.htm

Thanks again!

The sub-query method isn't guaranteed to return a consistent row of data when there is no unique maximum value when using GROUP BY. That's why I'm suggesting that an alternative syntax is needed.

I don't see how the ROW() function would be any different in that regard?

The idea is that it would. That's what I'm proposing.