title: MySQL select minimum value from a table with a group by
body: <p>Hey everyone<br />
  I have a table with an auto increment value called id, an user-id, a date (called date) and an integer for multiple registrations (called multiregistration)<br />
  As multiregistration can have more than one date I need to select the multiregistrations with the lowest date first<br />
  You can compare it by getting the minimum date for each multiregistration, where I need to get the (id for) each multiregistration. It should then sort them by the minimum date</p>
  
  <p><em>Some sample data</em></p>
  
  <pre><code> id --- user-id ---   date     --- multiregistration
   1   |    2      |  2010-02-01  |  1
   2   |    3      |  2010-02-01  |  2
   3   |    4      |  2010-01-01  |  2
   4   |    2      |  2010-02-03  |  1
   5   |    4      |  2010-02-03  |  3
   6   |    1      |  2010-02-02  |  3
  </code></pre>
  
  <p><em>Expected output</em></p>
  
  <pre><code> multiregistration
    2
    1
    3
  </code></pre>
  
  <p>You can compare it to</p>
  
  <pre><code> SELECT `multiregistration`
   FROM `registrations`
   ORDER BY `date`
  </code></pre>
  
  <p>But I want to remove duplicates</p>
  
  <p>Here is the PHP code I am using right now</p>
  
  <pre><code> // Create the query
   $query  = 'SELECT `multiregistration` AS `multireg` FROM `registrations` WHERE `multiregistration`&gt;0 GROUP BY `multiregistration`';
  
   // Execute the query and set the result into a variable
   $idsTo2ndQuery = arrayExecuteLocalQueryGetResults($query);
  
   // Create the second Query
   $query2 = 'SELECT `multiregistration` AS `multireg` FROM `registrations` WHERE `multiregistration`='.implode(' OR `multiregistration`=', $idsTo2ndQuery).' ORDER BY `date` ASC';
  
   // An array of IDs (sorted)
   $ids = array();
  
   // Execute the query and set the result into a variable
   $idsArray = arrayExecuteLocalQueryGetResults($query2);
  
   // For each entry
   foreach ($idsArray AS $idArray)
        if (!in_array($idArray, $ids))
             $ids[] = $idArray;
  
   // Return the IDs
   return $ids;
  </code></pre>