title: Querying a table with max() yealds strange results
body: <p>I have a table with structure like</p>
  
  <blockquote>
    <p>id, href(string), active_users(integer), measured(datetime)</p>
  </blockquote>
  
  <p>This table contains measurements for number of active users for a given site at the given time (hourly, i.e, there is historical data and you CAN have more than one row with identical href, but it will have a measured value).</p>
  
  <p>I want to get the maximum number of active users for each href in a given interval along with when was this peak reached (i.e, in this month, tell me when did each href have it's peak active users and when).</p>
  
  <p>The query I initially wrote looks like this:</p>
  
  <pre><code>SELECT href,
         max(active_users),
         measured AS peak_date
  FROM users_peak
  WHERE measured &gt;= DATE('2016-09-07')            
        AND measured &lt;= DATE('2016-10-07') 
  GROUP BY href
  </code></pre>
  
  <p>The problem is that in the results, the date is not the date that this peak was reached. If I do a manual check (<code>SELECT * FROM users_peak WHERE href='one_of_the_hrefs_from_the_result' AND measured &gt;= DATE('2016-09-07') AND measured &lt;= DATE('2016-10-07')</code> and order it by the active_users value), the date in the records that corresponds to the peak is different. When I modify the query to look like this:</p>
  
  <pre><code>SELECT od.href,
         od.active_users,
         oddate.measured AS measured
       FROM users_peak AS oddate
       JOIN
           (SELECT href,
                   max(active_users) AS active_users
            FROM users_peak
            WHERE measured &gt;= DATE('2016-09-07')
              AND measured &lt;= DATE('2016-10-07')
            GROUP BY href) AS od ON od.href = oddate.href
       WHERE oddate.active_users = od.active_users
           AND measured &gt;= DATE('2016-09-07')
           AND measured &lt;= DATE('2016-10-07')
       GROUP BY od.href
  </code></pre>
  
  <p>It returns the right results. Why is the initial query returning non-corresponding dates?</p>