title: MySQL group by and max returns wrong rows
body: <p>I have two tables and I try to find the "post" with the highest score per day.</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `posts_points` (
    `post_id` int(10) unsigned NOT NULL,
    `comments` smallint(5) unsigned NOT NULL,
    `likes` smallint(5) unsigned NOT NULL,
    `favorites` smallint(5) unsigned NOT NULL,
     PRIMARY KEY (`post_id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  
  
  CREATE TABLE IF NOT EXISTS `posts` (
    `profile_id` int(10) unsigned NOT NULL,
    `post_id` int(10) unsigned NOT NULL,
    `pubdate_utc` datetime NOT NULL,
    PRIMARY KEY (`post_id`),
    KEY `profile_id` (`profile_id`),
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  </code></pre>
  
  <p>I have tried the query below. It returns the correct score but the other columns are just random rows. What am I doing wrong ?</p>
  
  <pre><code>SELECT p.post_id, p.profile_id
     , MAX(t1.score)
     , DATE_FORMAT(t1.pubdate_utc, '%d %b') post_date
     , DATE(t1.pubdate_utc) mydate
  FROM
  (
     SELECT p.profile_id, p.post_id, p.pubdate_utc
        , (pp.comments + pp.likes + pp.favorites) AS score
     FROM posts p 
     INNER JOIN posts_points pp ON p.post_id = pp.post_id
  ) t1
  INNER JOIN posts p ON t1.post_id = p.post_id
     AND t1.pubdate_utc = p.pubdate_utc
  GROUP BY mydate
  ORDER BY mydate DESC
  LIMIT 18;
  </code></pre>