6

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 |
+------+--------+----------+------+--------+-------+------+------------+
11

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

| improve this answer | |
5

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.

| improve this answer | |
  • @TypoCubeᵀᴹ: I don't do that [ORDER BY 1] very often... and I agree it might be missleading, but it is legal (and short). – joanolo Jan 6 '17 at 21:37
  • Legal it may be but it depends on the order of the columns in the SELECT list. And here you have SELECT p.* so we don't even know which column is first. Not a query I'd like to read or have in my code. Just my opinion, you can keep it if you like, it's your answer. – ypercubeᵀᴹ Jan 6 '17 at 21:47
  • 1
    @TypoCubeᵀᴹ: I already changed at your first suggestion... I agree with you, and don't fully understand why it is legal. I must add that, in prodcution code, I would also avoid p.* if p is a table; and use them selectively if p is a subquery. But in an example, where I really don't have the structure of the p table... – joanolo Jan 6 '17 at 22:09
0

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;
| improve this answer | |
  • Can you share a link to documentation for "The grouping unifies in a row the records with the same value (s) defined in the GROUP BY statement. When MySQL does this, it keeps the first record." ? – enharmonic Feb 19 at 22:04
  • 1
    In fact, the server is free to choose (nondeterministic) which value to return for non-aggregated columns. Follow the doc, see the 7th paragraph. I was influenced because in several tests with mysql it always returned the first line and there is no reference to that in the doc. Anyway, the solution is valid because it orders first and then groups (it can be done with subquerys instead of a temporary table). I will correct my answer. – dh117 Feb 26 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.