15/05/2018 - MYSQL
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.
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 |
+----+-----------+---------+-------+--------------+------+
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 |
+----+-----------+---------+-------+------+
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;