4

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

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

4

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.

  • @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 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. Imagine that he holds the first record of that value(s) and discards the others, and only retains the value of the MIN statement, until it completes a group, and so on. In a version of MariaDB (I do not know specifically which), this occurs also, though it keeps the last record instead of the first one.

To solve this, simply create a temporary table, 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;

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.