title: SQL MIN() & GROUP BY
body: <p>I'm searching for a more elegant solution to a problem I'm having with MySQL MIN() used in conjunction with GROUP BY.</p>
  
  <p>I have two tables:</p>
  
  <ol>
  <li>products (id, group_id)</li>
  <li>product_attributes (product_id, attribute_id, value)</li>
  </ol>
  
  <p>I wish to select the cheapest metal out of each metal group (attribute_id = 40) from a certain product group.</p>
  
  <p>The following query works but I'm quite certain it could have been written much better.</p>
  
  <pre><code>SELECT metal.product_id, metal.value AS `metal`, `price`.value AS `price`
  FROM
      product_attributes metal
  INNER JOIN
      (
          SELECT metal.value AS `metal`, MIN(price.value) AS `price`
          FROM 
              product_attributes metal
          LEFT JOIN 
              product_attributes price
          ON metal.product_id = price.product_id
          INNER JOIN products p
          ON p.id = metal.product_id
          WHERE
              metal.attribute_id = 40 AND
              price.attribute_id = 37 AND
              p.group_id = 1
          GROUP BY
              metal.value
          ORDER BY metal ASC, price DESC
      ) AS d ON
      d.metal = metal.value
  LEFT JOIN
      product_attributes price ON
      metal.product_id = price.product_id AND price.attribute_id = 37
  INNER JOIN
      products p ON
      p.id = metal.product_id
  WHERE
      metal.attribute_id = 40
      AND d.price = price.value
      AND p.group_id = 1
  GROUP BY
      metal.value
  ORDER BY
      metal.value ASC,
      price.value DESC;
  </code></pre>