Bug #23462 Group BY and MAX not selecting MAX result
Submitted: 19 Oct 2006 13:42 Modified: 14 Jun 2007 14:21
Reporter: Joseph Wilk Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:mysql 5.0 OS:Any
Assigned to: CPU Architecture:Any

[19 Oct 2006 13:42] Joseph Wilk

Following the example from http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group.html

I'm tried to use the GROUP BY and MAX function to select the Maximum Column per Group. 

SELECT article,
       timestamp as old,
       MAX(timestamp) AS timestamp
FROM   shop_new
GROUP BY article

Using this SQL I get the result ->
article	old	timestamp
1	2	4
2	6	15
3	1	5

It appears that the the max timestamp field is correct but the old value can be seen to not match the timestamp field. Hence the articles we have been given do not have the max value. Its almost as if the max value is just added on

I would expect the result
article	old	timestamp
1	4	4
2	15	15
3	5	5

How to repeat:

CREATE TABLE `shop_new` (
  `article` int(11) default NULL,
  `timestamp` int(11) default NULL
INSERT INTO `shop_new` (`article`, `timestamp`) VALUES 

SELECT article,
       timestamp as old,
       MAX(timestamp) AS timestamp
FROM   shop_new
GROUP BY article

Suggested fix:
? Sorry no idea!
[19 Oct 2006 15:16] Valeriy Kravchuk
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ (http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html, to be precise) and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php
[20 Apr 2007 21:45] Predag Krstić
This is a bug, and it is fixed with adding LIMIT x (x is any number)
[21 Apr 2007 18:36] Predag Krstić
Sorry for previous comment, but with addition of LIMIT does not solve problem.
[14 Jun 2007 14:21] Valeriy Kravchuk
This is not a bug. Please, re-read the manual, http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html, carefully:

" Do not use this feature if the columns you omit from the GROUP BY part are not constant in the group. The server is free to return any value from the group, so the results are indeterminate unless all values are the same."

Exactly the case here.