title: MySQL left join with lowest value of a group
body: <p>I have two tables. Table one consists of all the metrics. Table two consists of all the articles.</p>
  
  <p>I'm trying to get all the rows from the metric table extended with brand, priority and national_code of the corresponding article with the lowest priority.  </p>
  
  <pre><code>SELECT national_code, brand, width, height, inch, `load`, speed, season, rof, MIN(`prio`) as prio FROM `whitelistarticles`
                              WHERE prio &lt; 1000
                              GROUP BY
                              width, height, inch, `load`, speed,season,rof;
  
                              SELECT metrics.*, articles.national_code, articles.prio, articles.brand FROM whitelistmetrics as metrics
                              LEFT JOIN (SELECT national_code, brand, width, height, inch, `load`, speed, season, rof, MIN(`prio`) as prio FROM `whitelistarticles`
                              WHERE prio &lt; 1000
                              GROUP BY
                              width, height, inch, `load`, speed,season,rof) as articles on
                              metrics.width = articles.width AND
                              metrics.height = articles.height AND
                              metrics.inch = articles.inch AND
                              metrics.load = articles.load AND
                              metrics.speed = articles.speed AND
                              metrics.season = articles.season AND
                              metrics.rof = articles.rof
                              WHERE articles.prio IS NOT NULL
                              ORDER BY width, height, inch, `load`, speed;
  </code></pre>
  
  <p>The issue is that this query gives me the lowest priority of a group, but not the row with the priority. It ends up giving me wrong brands and wrong national_codes while it picked the lowest priority of the group. </p>
  
  <p>.e.g. two articles with the corresponding metrics, one has a priority of 20 and the other 15. I want the brand and national_code of the article with the priority of 15. It ends up giving me a priority of 15, with the national_code and brand name of the article with a priority of 20.</p>
  
  <p>How do I fix this query so that it will join the row with the lowest priority instead of only the priority?</p>