GROUP BY gives wrong result with MIN() aggregate function
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 ontotal
- Join it back to the product table on
code
andprice
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
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.