GROUP BY gives wrong result with MIN() aggregate function

I have a table that looks like this:

+------+--------+----------+------+--------+-------+------+------------+
| id | code | category | mq | weight | weave | show | min(price) |
+------+--------+----------+------+--------+-------+------+------------+
| 1 | DT450R | carbon | 1 | 450 | plain | 1 | 90 |
| 2 | DT450R | carbon | 2 | 450 | plain | 1 | 40 |
| 3 | DT450R | carbon | 5 | 450 | plain | 1 | 75 |
| 7 | PP120Q | carbon | 3 | 120 | twill | 1 | 28 |
| 8 | PP120Q | carbon | 7 | 120 | twill | 1 | 65 |
| 9 | PP120Q | carbon | 9 | 120 | twill | 1 | 49 |
| 4 | ZX300R | carbon | 1 | 300 | plain | 0 | 12 |
| 5 | ZX300R | carbon | 15 | 300 | plain | 1 | 128 |
| 6 | ZX300R | carbon | 30 | 300 | plain | 1 | 92 |
+------+--------+----------+------+--------+-------+------+------------+

I've created a sqlfiddle here.

I want min price from table in each code. I tried using the following query:

select id, code, category, mq, weight, weave, price, `show`, min(price) as total 
from product group by code;

Why is the group by getting the wrong result? It's returning id = 1 instead of id =2.

Incorrect output:

+------+--------+----------+------+--------+-------+------+------------+
| id | code | category | mq | weight | weave | show | min(price) |
+------+--------+----------+------+--------+-------+------+------------+
| 1 | DT450R | carbon | 1 | 450 | plain | 1 | 40 |
| 7 | PP120Q | carbon | 3 | 120 | twill | 1 | 28 |
| 4 | ZX300R | carbon | 1 | 300 | plain | 0 | 12 |
+------+--------+----------+------+--------+-------+------+------------+

Expected output:

+------+--------+----------+------+--------+-------+------+------------+
| id | code | category | mq | weight | weave | show | min(pr

As a MySQL DBA, I sadly admit that MySQL can be rather cavalier in its SQL processing. One of the most infamous feats of this is its GROUP BY behavior.

As example, Aaron Bertrand answered the post Why do we use Group by 1 and Group by 1,2,3 in SQL query? where he described MySQL's GROUP BY as cowboy who-knows-what-will-happen grouping. I just had to agree.
SUGGESTION

Rewrite the GROUP BY using code

select code,min(price) as total 
from product group by code

Do three things

• Make the query a subquery

• Use price as alias instead on total

• Join it back to the product table on code and price

Here is the proposed query

select b.* from
(select code,min(price) as price from product group by code) a
inner join product b using (code,price);

or

select b.* from
(select code,min(price) as price from product group by code) a
inner join product b ON a.code=b.code AND a.price=b.price;

Checkout the SQL Fiddle for this
GIVE IT A TRY !!!
UPDATE 2017-01-06 16:17 EST

If there exists more than 1 row with the same minimum price for a given code, you have take the query, make it a subquery, join it to retrieve the minimum id for each (code,price) and join that back to product by id:

select bb.* from
(select a.code,a.price,min(b.id) id from
(select code,min(price) as price from product group by code) a
inner join product b using (code,price)
group by a.code,a.price) aa
inner join product bb using (id);

Checkout the SQL Fiddle for that

StackExchange Database Administrators Q#160216, answer score: 11