Mysql – Sub query not working as expected trying to order by then group by, MySQL 5.5 vs 5.7

MySQLmysql-5.7

I do not know/understand DB's as well as I would like. I'm trying to ORDER BY and then GROUP BY so that I get the first record from each group of results in the database.

-- Example table (just an example. real table may contain hundreds of rows where field 2 is a duplicate but field 3 and 4 are different.)

CREATE TABLE IF NOT EXISTS `docs` (
  `field1` int(6) unsigned NOT NULL,
  `field2` int(6) unsigned NOT NULL,
  `field3` int(6) NOT NULL,
  `field4` int(6) NOT NULL,
  PRIMARY KEY (`field1`)
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`field1`, `field2`, `field3`, `field4`) VALUES
  ('27908', '82', '1', '17'),
  ('27907', '82', '1', '50'),
  ('402', '25', '1', '90'),
  ('312', '25', '10', '8');

If I ORDER BY the results are as expected. Note it works without sub-query. I'm just breaking apart my original query:

SELECT * FROM 
(
SELECT * FROM docs
ORDER BY field2, field1 DESC
)

Result (formmated with table generator):

+--------+--------+--------+--------+
| field1 | field2 | field3 | field4 |
+--------+--------+--------+--------+
|    402 |     25 |      1 |     90 |
|    312 |     25 |     10 |      8 |
|  27908 |     82 |      1 |     17 |
|  27907 |     82 |      1 |     50 |
+--------+--------+--------+--------+

The ORDER BY then GROUP BY works as expected in MySQL v5.5:

SELECT * FROM 
(
SELECT * FROM docs
ORDER BY field2, field1 DESC
) temp
GROUP BY field2

Result:

+--------+--------+--------+--------+
| field1 | field2 | field3 | field4 |
+--------+--------+--------+--------+
|    402 |     25 |      1 |     90 |
|  27908 |     82 |      1 |     17 |
+--------+--------+--------+--------+

But it no longer works in v5.7 and results in error:

Query Error: Error: ER_WRONG_FIELD_WITH_GROUP: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'temp.field1' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I don't understand how to fix when using a sub-query or another way to write the query that works in v5.7. How can this query be changed to work in MySQL v5.7?

Best Answer

You are correct, this was different in earlier versions

...However, in MySQL prior to version 5.7 the default behaviour has been more forgiving because it will not complain and then arbitrarily choose any value! It also has an ANY_VALUE() function that could be used as another solution to this question if you really needed the same behaviour as before...

Source

But this change is a good thing, as the error used to be silenced. I would recommend changing the query to get the results you want. I believe you want the one where field1 is the largest.

Field1 is the primary key, so you could possibly do something like the following

SELECT docs.field1,docs.field2,docs.field3,docs.field4
FROM 
(
SELECT max(field1) as field1 ,field2 FROM docs
GROUP BY field2
) temp
INNER JOIN docs
on temp.field1 = docs.field1 
ORDER BY field2, field1 desc;

DBFiddle

The other way around this are to either disable the functionality or use ANY_VALUE() but I would advise against that.

ANY_VALUE() can give an inconsistent resultset, an example could be the number 50 instead of the expected 17 as field4 where the field1 column = 27908.

DBFiddle

Related Question