title: Select row with distinct value and max date
body: <p>I have a MySQL (5.1.49) table with three columns.</p>
  
  <pre><code>mysql&gt; create table testme(id int auto_increment, id2 int not null, somedate datetime, primary key(id));
  </code></pre>
  
  <p>In my case <strong>id2</strong> is not unique, but I want to return rows with distinct <strong>id2</strong> values with the max <strong>somedate</strong>.</p>
  
  <p>Here's some sample data.</p>
  
  <pre><code>mysql&gt; insert into testme values (1, 5, '2012-01-02 01:01:01'),(2, 5, '2012-02-02 02:02:02'),(3, 7, '2010-01-10 11:01:33');
  </code></pre>
  
  <p>This <a href="https://stackoverflow.com/questions/9038980/top-1-query-from-each-id-with-multiple-instances">question</a> almost answers mine, but with the extra id field, the returned <strong>id</strong> and <strong>id2</strong> don't match. For <strong>id2=5</strong>, it's returning <strong>id=1</strong> instead of <strong>id=2</strong>.</p>
  
  <pre><code>mysql&gt; select id, id2, max(somedate) from testme group by id2;
  +----+-----+---------------------+
  | id | id2 | max(somedate)       |
  +----+-----+---------------------+
  |  1 |   5 | 2012-02-02 02:02:02 |
  |  3 |   7 | 2010-01-10 11:01:33 |
  +----+-----+---------------------+
  </code></pre>
  
  <p>I'm expecting,</p>
  
  <pre><code>+----+-----+---------------------+
  | id | id2 | max(somedate)       |
  +----+-----+---------------------+
  |  2 |   5 | 2012-02-02 02:02:02 |
  |  3 |   7 | 2010-01-10 11:01:33 |
  +----+-----+---------------------+
  </code></pre>
  
  <p>Want the ID that matches maximum date for each ID2</p>
  
  <p>Does anyone have any ideas please? Thanks</p>