Hello everyone!

We have been investing plenty of personal time and energy for many years to share our knowledge with you all. However, we now need your help to keep this blog running. All you have to do is just click one of the adverts on the site, otherwise it will sadly be taken down due to hosting etc. costs. Thank you.

MySQL 5.7 and onwards version enables ONLY_FULL_GROUP_BY which was disabled by default before. If you use GROUP BY clause in your SQL statements, you will probably get a warning message like below.


[42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

You can get rid of the warning by disabling ONLY_FULL_GROUP_BY option. However, I would suggest you not to because it was added for good causes. In this example we will see how you should construct your query instead.


Database


mysql> DESCRIBE fruit;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| origin | varchar(100) | YES | | NULL | |
| price | double | YES | | NULL | |
| is_available | int(1) | YES | | NULL | |
| year | varchar(4) | YES | | NULL | |
+--------------+------------------+------+-----+---------+----------------+

mysql> SELECT * FROM fruit;
+----+-----------+---------+-------+--------------+------+
| id | name | origin | price | is_available | year |
+----+-----------+---------+-------+--------------+------+
| 1 | Apple | Germany | 1.99 | 1 | 2018 |
| 2 | Banana | Jamaica | 2.01 | 1 | 2018 |
| 3 | Orange | Germany | 1.88 | 1 | 2017 |
| 4 | Cherry | Spain | 2.04 | 0 | 2017 |
| 5 | Melon | Spain | 2.77 | 1 | 2018 |
| 6 | Pear | France | 0.99 | 0 | 2018 |
| 7 | Plum | Turkey | 2.11 | 1 | 2018 |
| 8 | Peach | Turkey | 2.23 | 0 | 2017 |
| 9 | Pineapple | Kenya | 3.01 | 1 | 2018 |
| 10 | Banana | Denmark | 2.44 | 1 | 2019 |
+----+-----------+---------+-------+--------------+------+

Our aim and expected result


Select a few fields, group them by a single field and order the result based on multiple fields. Expected result is shown below.


+----+-----------+---------+-------+------+
| id | name | origin | price | year |
+----+-----------+---------+-------+------+
| 10 | Banana | Denmark | 2.44 | 2019 |
| 1 | Apple | Germany | 1.99 | 2018 |
| 2 | Banana | Jamaica | 2.01 | 2018 |
| 5 | Melon | Spain | 2.77 | 2018 |
| 9 | Pineapple | Kenya | 3.01 | 2018 |
| 7 | Plum | Turkey | 2.11 | 2018 |
+----+-----------+---------+-------+------+

Query


The critical point here is to use ANY_VALUE function. You can also use aggregate functions such as AVG(), MIN(), MAX() as well as GROUP_CONCAT function.


mysql> SELECT
-> ANY_VALUE(`id`) AS `id`,
-> ANY_VALUE(`name`) AS `name`,
-> `origin`,
-> ANY_VALUE(`price`) AS `price`,
-> ANY_VALUE(`year`) AS `year`
-> FROM `fruit`
-> WHERE `is_available` = 1
-> GROUP BY `origin`
-> ORDER BY `year` DESC, `name` ASC;