Use of MIN() to select just one record per code (MySQL)

Question

[Foreword for the compulsives -1] I know this question has been answered, at least, a billion times, but the problem is that I can't model those answers to what I wanna obtain. I'm not an SQL expert, that's sure; I'm confident just with the classical commands like SELECT, UPDATE, DELETE, ecc. so I'm gonna thank anyone who will like to help me.

Said that, let's suppose I have a table like this one:

|----|--------|------------|----|----------|---------|---------|------|
| id |  code  |  category  | mq |  weight  |  weave  |  price  | show |
|----|--------|------------|----|----------|---------|---------|------|
| 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  |

What I would like my query to do is to select, for each code, just the row with the minimum price:

| 2  | DT450R |   carbon   |  2 |   450    |  plain  |    40   |   1  |
| 4  | ZX300R |   carbon   |  1 |   300    |  plain  |    12   |   0  |
| 7  | PP120Q |   carbon   |  3 |   120    |  twill  |    28   |   1  |

First attempt (based on the explanation of MIN() given in MySQL documentation or, at least, on what I understood of it):

$sql = 'SELECT code, weight, weave, MIN(price)
        FROM products
        WHERE category="carbon" AND show="1"
        GROUP BY code
        ORDER BY weight ASC';

Second attempt (based on this answer here on SO):

$sql = 'SELECT a.code, a.weight, a.price, a.weave
        FROM   products a
        INNER JOIN
        (
            SELECT   code, weight, MIN(price) AS minprice, weave
            FROM     products
            GROUP BY code
        ) 
       b ON a.code = b.code AND a.weave = b.weave AND a.price = b.minprice AND AND a.weight = b.weight
       WHERE category="carbon" AND show="1"
       ORDER BY a.weight ASC';

Third attempt (based on this other answer here on SO):

$sql = 'SELECT code, weight, weave, price
        FROM products
        INNER JOIN 
        (
             SELECT MIN(price) price, code, weight, weave
             FROM products
             GROUP BY code
        ) 
        AS MIN ON MIN.code = products.code AND MIN.weight = products.weight AND MIN.weave = products.weave
        WHERE category="carbon" AND show="1"
        ORDER BY a.weight ASC';

It's probably useless say that none of these attempts produced the expected result; just the third method outputs something while the others two return 0 matches. I understood that in the 2nd and 3rd methods I'm nesting a query into a query but I can't figure out why they don't work.

Homework, but at least you're trying. Third attempt is right path, but you don't need anything but the code and min(price) in your derived table, which you can then join to the original table on code and WHERE price = derived.price — Chris Caviness, Jan 06 '17 at 18:18
Actually, the second attempt is closest. But get rid of `weave` and `weight` from the subquery and `ON`. — Barmar, Jan 06 '17 at 18:22
Why does your result include `id = 4`? Your query says `AND show = "1"`, not that row has `show = "0"`. — Barmar, Jan 06 '17 at 18:25

Barmar · Accepted Answer · 2017-01-06T18:30:49.070

3

You're close with your second attempt. But the only columns you should be joining on are code and price. weight and weave then come from the row that's selected by this join condition.

SELECT a.code, a.weight, a.price, a.weave
FROM   products a
INNER JOIN
(
    SELECT   code, MIN(price) AS minprice
    FROM     products
    GROUP BY code
) 
b ON a.code = b.code AND a.price = b.minprice
WHERE category="carbon" AND show="1"
ORDER BY a.weight ASC

This is the same as the answers in the questions you linked to. None of them suggest adding other columns to the ON clause, so I'm not sure where that came from.

edited Jan 06 '17 at 18:30

answered Jan 06 '17 at 18:24

Barmar

741,623
53
500
612

This is wrong, since you join on code and price , you need to join on id. Otherwise you might get duplicates in your result. – Joel Harkes Jan 06 '17 at 18:26
You can't join on ID, because the subquery can't return the ID. If you select ID in the subquery, it will just pick a random ID from the group. You can only get duplicates in the result if there are two rows with the same code and the same lowest price. – Barmar Jan 06 '17 at 18:27
1

Now, @brigo, please explain why this is the correct answer... I don't mind helping with your homework as long as you walk away comprehending. – Chris Caviness Jan 06 '17 at 18:32
It's still terrible (in general) because most probably no index on those fields + you don't expect duplicates. Check this: http://stackoverflow.com/questions/1895110/row-number-in-mysql – Joel Harkes Jan 06 '17 at 18:32
@JoelHarkes The duplicates can be solved by using `GROUP BY a.code` in the outer query. Other solutions also suffer from performance problems if you don't have appropriate indexes. – Barmar Jan 06 '17 at 18:33
@ChrisCaviness That's exactly what I'm trying to do ;) 'cause I tried and it perfectly works, but "going away" with this and nothing learned == tomorrow I'm still here asking a similar question. I now got that with this code I'm making two queries linking them after on code and price, but what I didn't really get is: with the second select, I'm saying: *"SELECT code, MIN(price) **among** the results of the first query"* and not *"among the whole table"*, right? – Brigo Jan 06 '17 at 18:48
1

No, the subquery operates on the whole table. The JOIN is what then combines it with the main query. – Barmar Jan 06 '17 at 18:50
@Barmar Then why if the subquery operates on the whole table, the result can't be achieved just using a syntax similar to the first attempt? It's quite complicated to me 'cause before I thought I could ask something like *"SELECT the elements of the row with the MIN(price) per each code"*, but as far as I understood the only correct way is asking *"SELECT the elements, SELECT the row with the MIN(price) for each code, then bind the results"*. If so, the first processed query should be the one nested `SELECT code, MIN(price) AS minprice`, right? – Brigo Jan 06 '17 at 19:02
1

The first attempt doesn't work because when you select the other columns like `weight`, it just gets that from an arbitrary row from the group, not the same row as the one with `MIN(price)`. The JOIN is what selects a specific row for the other columns. – Barmar Jan 06 '17 at 19:04
1

The subquery gives you a second set (derived table) consisting only of the code and the minimum price. The inner join between the source table and the derived table says "give me a third subset with only the rows where a condition is true" or basically C = conditional(A intersection B). Your columns list determines which fields from A or B will be returned in the new set C. – Chris Caviness Jan 06 '17 at 19:14
https://en.wikipedia.org/wiki/Set_(mathematics) SQL simplified: -- SELECT = define a set, FROM table = get predefined set, JOIN = intersection, UNION = union, WHERE = limit the input set by some condition, HAVING = limit the final result set by some condition – Chris Caviness Jan 06 '17 at 19:30
@ChrisCaviness With the sets explication everything is definitely clearer. With the secondSELECT I'm so giving the conditions of the search into the main set operated by the first SELECT. I could then add another discriminator if I would like and need into the second SELECT, right? (not with the table I wrote for example, but in general) Like adding mq in the second SELECT I would say *"SELECT the rows with the MIN(price) per each different code and each different mq"*, right? – Brigo Jan 06 '17 at 20:02
1

That is correct. Min, Max, Count, Sum, these are aggregating functions that will return a single value from the entire set of values based on the group by condition. The group by determines when you will "reset" that aggregate and generate a new row. – Chris Caviness Jan 06 '17 at 20:44
@ChrisCaviness The set explanation has been definitely useful: I changed a bit the query to avoid a problem with showX column's values and it worked just because of that explanation of the how-it-works concept. Thanks again Chris! And many thanks to Barmar as well of course! – Brigo Jan 07 '17 at 13:24

Use of MIN() to select just one record per code (MySQL)

1 Answers1