MySQL problems with the group in the new versions

Question

Good day.

Note: In MySQL 5.7.0 and newer is included by default in ONLY_FULL_GROUP_BY sql_mode

The following query is not:

SELECT
    `ordered_list`.*
FROM
    (
        SELECT
            *
        FROM
            `test`
        ORDER BY
            `test`.`datetime` DESC
    ) `ordered_list`
GROUP BY
    `ordered_list`.`parent_id`

Gives an error message Error: Expression # 1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ordered_list.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode = only_full_group_by to be expected.

I get the following result if you disable this option:

Id  parent_id   datetime                field1          field2
1   1           2016-04-12 13:01:15     value 1-1-1     value 1-1-2
4   2           2016-04-26 13:08:31     value 2-4-1     value 2-4-2

MySQL versions prior to 5.7.x returns a desired result:

Id  parent_id   datetime                field1          field2
4   2           2016-04-26 13:08:31     value 2-4-1     value 2-4-2
2   1           2016-04-19 13:02:03     value 1-2-1     value 1-2-2

Source data:

Id  parent_id   datetime                field1          field2
1   1           2016-04-12 13:01:15     value 1-1-1     value 1-1-2
2   1           2016-04-19 13:02:03     value 1-2-1     value 1-2-2
3   1           2016-04-05 13:07:54     value 1-3-1     value 1-3-2
4   2           2016-04-26 13:08:31     value 2-4-1     value 2-4-2
5   2           2016-04-19 13:09:24     value 2-5-1     value 2-5-2
6   2           2016-04-20 13:12:46     value 2-6-1     value 2-6-2

As should look query to select rows the data which appropriate in the maximum date in each group?

MySQL problems with the group in the new versions

0 Answers0