使用MIN（）为每个代码选择一条记录（MySQL）[重复]

Question

This question already has an answer here:

• SQL Group By and min (MySQL) 3 answers

[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.

</div>

Answer 1

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.