title: Get row with highest or lowest value from a GROUP BY
body: <p>I'm trying to get the row with the highest/lowest number, after performing a <code>GROUP BY</code>:</p>
  
  <p>Here is my test data</p>
  
  <pre><code>mysql&gt; SELECT * FROM test;
  +----+-------+------+
  | id | value | name |
  +----+-------+------+
  |  1 |    10 | row1 |
  |  2 |    12 | row2 |
  |  3 |    10 | row2 |
  |  4 |     5 | row2 |
  +----+-------+------+
  4 rows in set (0.00 sec)
  </code></pre>
  
  <p>To get the lowest value, I'll use <code>MIN()</code></p>
  
  <pre><code>mysql&gt; SELECT id, name, MIN(value) AS value FROM test GROUP BY name;
  +----+------+-------+
  | id | name | value |
  +----+------+-------+
  |  1 | row1 |    10 |
  |  2 | row2 |     5 |
  +----+------+-------+
  2 rows in set (0.00 sec)
  </code></pre>
  
  <p>Now, the id <code>row2</code> is <code>2</code>, but it should be <code>4</code>.</p>
  
  <p>I also tried with a join:</p>
  
  <pre><code>mysql&gt; SELECT t1.* FROM 
         (SELECT id, name, MIN(value) AS value 
            FROM test GROUP BY name) AS t1 
         INNER JOIN test AS t2 ON t1.id = t2.id;
  +----+------+-------+
  | id | name | value |
  +----+------+-------+
  |  1 | row1 |    10 |
  |  2 | row2 |     5 |
  +----+------+-------+
  2 rows in set (0.00 sec)
  </code></pre>
  
  <p>How can I get the correct ID for each result based on what the lowest <code>value</code> is?</p>