Subquery ORDER BY doesn't work on MySQL 5.6, but works on 5.5

I try to join 3 tables (products, prices, categories) together to get a result of latest & lowest prices from the joined tables.

I was able to get the expected result on MySQL 5.5, but after upgrading to 5.6, ORDER BY on the subquery seems be ignored. How to change the query to make the ORDER BY work?

The query is supposed to:

• find products which is under specified category id (eg: category id 488).

• find the latest price of each product.

• group products with same 'matchkey' (matchkey generated by removing stopwords from the product name. generated by other programming)

• find the lowest price of each grouped products

• show each grouped products info with lowest price

Incorrect result:

The 3 tables relationship are as below:

• products many-to-many categories

• products -> one-to-many -> prices

table information:

```
mysql> show columns from products;
+----------------+------------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(300) | NO | MUL | NULL | |
| active | tinyint(1) | YES | | 1 | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| updated_at | timestamp | NO | | 0000-00-00 00:00:00 | |
| matchkey | varchar(300) | NO | MUL | NULL | |
| sku | varchar(50) | YES | MUL | NULL | |
+----------------+------------------+------+-----+---------------------+---------

SELECT a, b ...
    GROUP BY a

is asking for trouble. You may have been 'lucky' in 5.5 and 'unlucky' in 5.6.

The problem is that any value of b can be shown for each 'group'. Sure, there are cases where a given a maps uniquely to a given b (eg, in a normalization table), but that does not feel like the case here. To see that there is a problem:

SET sql_mode = 'ONLY_FULL_GROUP_BY';

before doing the SELECT.

See also: http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_only_full_group_by

You really should switch to InnoDB.

When you get to 5.7, that setting will be defaulted on.

Efficient techniques for groupwise max.

StackExchange Database Administrators Q#127182, answer score: 2