GROUP BY gives wrong result with MIN() aggregate function

Question

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(price) |
+------+--------+----------+------+--------+-------+------+------------+
|    2 | DT450R | carbon   |    2 |    450 | plain |    1 |         40 |
|    4 | ZX300R | carbon   |    1 |    300 | plain |    0 |         12 |
|    7 | PP120Q | carbon   |    3 |    120 | twill |    1 |         28 |
+------+--------+----------+------+--------+-------+------+------------+

Answer 1

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

1. Make the query a subquery
2. Use price as alias instead on total
3. 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

Answer 2

That should do the trick:

SELECT
    p.*
FROM
    product p
    JOIN
    (
        SELECT
            code, min(price) AS min_price
        FROM
            product
        GROUP BY
            code
    ) m ON p.code = m.code AND p.price = m.min_price 
ORDER BY
    p.id ;

Caveat: if there are ties (i.e.: the min(price) appears in more than one row per group), all rows will be returned. If, in case of a tie, you want another behaviour, things get a bit more complicated... a second choice criterium is needed (if possible, one that cannot get another tie), and another level of subquerying.

You can check this query also @ SQLFiddle

You can check all @RolandoMySQLDBA explanations for all tne "non-standard" things that go behind a GROUP BY in mySQL. It can easily be tricky.

Answer 3

The question is old but since I did not see answers with different approaches I decided to respond with a simpler alternative (without using joins).

The grouping unifies the records with the same values recorded in the GROUP BY statement. When MySQL does this, it chooses a record to maintain (nondeterministic). Imagine that he holds one line and discards the others, just keeping the value of the MIN instruction, until he completes a group, and so on.

In several tests, MySQL always chose the values of the first line while MariaDB kept the last one. There is no reference to this in the doc and it just says that the server is free to choose any value for each group.

To solve this, simply create a temporary table (or subquerys), ordering the column to be used with MIN in ASC or DESC, according to the record that is kept (top or bottom) and the column to be grouped. Then, on a select, use the grouping with the MIN command.

See this fiddle.

CREATE TABLE product (`id` int, `code` varchar(6), `category` varchar(6), `mq` int, `weight` int, `weave` varchar(5), `price` int, `show` int);

INSERT INTO product
    (`id`, `code`, `category`, `mq`, `weight`, `weave`, `price`, `show`)
VALUES
    (1, 'DT450R', 'carbon', 1, 450, 'plain', 90, 1),
    (2, 'DT450R', 'carbon', 2, 450, 'plain', 40, 1),
    (3, 'DT450R', 'carbon', 5, 450, 'plain', 75, 1),
    (4, 'ZX300R', 'carbon', 1, 300, 'plain', 12, 0),
    (5, 'ZX300R', 'carbon', 15, 300, 'plain', 128, 1),
    (6, 'ZX300R', 'carbon', 30, 300, 'plain', 92, 1),
    (7, 'PP120Q', 'carbon', 3, 120, 'twill', 28, 1),
    (8, 'PP120Q', 'carbon', 7, 120, 'twill', 65, 1),
    (9, 'PP120Q', 'carbon', 9, 120, 'twill', 49, 1)
;

# RESOLUTION

# I have used non-temporary table due to the fiddle constraint.
CREATE table tbOrdened
select 
id, `code`, category, mq, weight, weave, `show`, price
from product
order by price asc, `code`;

SELECT idforn, idativo, min(valor) AS valor from tbOrdened as tb1 GROUP BY idativo;