title: MySQL GROUP BY grouping by lowest field value
body: <p>I'm trying to fetch the lowest price per day per hotel, I get multiple results.
  I first try to fetch the lowest amount with the MIN() function, then inner join.
  When i later try to group by outside the subquery, it just groups by the lowest id.
  The SQL itself:</p>
  
  <pre><code>SELECT mt.id, mt.amount, mt.fk_hotel, mt.start_date   
  FROM price mt 
  INNER JOIN
  (
      SELECT price.id, MIN(price.amount) minAmount
      FROM price
      WHERE 1=1 AND price.start_date &gt;= '2014-10-08' AND price.start_date &lt;= '2014-10-10' AND price.active = 1 AND price.max_people = 2
      GROUP BY id
  ) t 
  ON mt.id = t.id AND mt.amount = t.minAmount
  ORDER BY mt.fk_hotel, mt.amount;
  </code></pre>
  
  <p>And the results looks like this:
  <a href="http://jsfiddle.net/63mg3b2j/" rel="nofollow">http://jsfiddle.net/63mg3b2j/</a></p>
  
  <p>I want to group by the start date and fk_hotel so that it groups by the lowest amount value, can anybody help me? Am I being clear?</p>
  
  <p>Edit: I also need a field fk_room from the corresponding row, so i can inner join</p>