title: How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
body: <p>I have a table of player performance:</p>
  <pre><code>CREATE TABLE TopTen (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    home INT UNSIGNED NOT NULL,
    `datetime`DATETIME NOT NULL,
    player VARCHAR(6) NOT NULL,
    resource INT NOT NULL
  );
  </code></pre>
  <p>What query will return the rows for each distinct <code>home</code> holding its maximum value of <code>datetime</code>? In other words, how can I filter by the maximum <code>datetime</code> (grouped by <code>home</code>) and still include other non-grouped, non-aggregate columns (such as <code>player</code>) in the result?</p>
  <p>For this sample data:</p>
  <pre><code>INSERT INTO TopTen
    (id, home, `datetime`, player, resource)
  VALUES
    (1, 10, '04/03/2009', 'john', 399),
    (2, 11, '04/03/2009', 'juliet', 244),
    (5, 12, '04/03/2009', 'borat', 555),
    (3, 10, '03/03/2009', 'john', 300),
    (4, 11, '03/03/2009', 'juliet', 200),
    (6, 12, '03/03/2009', 'borat', 500),
    (7, 13, '24/12/2008', 'borat', 600),
    (8, 13, '01/01/2009', 'borat', 700)
  ;
  </code></pre>
  <p>the result should be:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>home</th>
  <th>datetime</th>
  <th>player</th>
  <th>resource</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>10</td>
  <td>04/03/2009</td>
  <td>john</td>
  <td>399</td>
  </tr>
  <tr>
  <td>2</td>
  <td>11</td>
  <td>04/03/2009</td>
  <td>juliet</td>
  <td>244</td>
  </tr>
  <tr>
  <td>5</td>
  <td>12</td>
  <td>04/03/2009</td>
  <td>borat</td>
  <td>555</td>
  </tr>
  <tr>
  <td>8</td>
  <td>13</td>
  <td>01/01/2009</td>
  <td>borat</td>
  <td>700</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I tried a subquery getting the maximum <code>datetime</code> for each <code>home</code>:</p>
  <pre><code>-- 1 ..by the MySQL manual: 
  
  SELECT DISTINCT
    home,
    id,
    datetime AS dt,
    player,
    resource
  FROM TopTen t1
  WHERE `datetime` = (SELECT
    MAX(t2.datetime)
  FROM TopTen t2
  GROUP BY home)
  GROUP BY `datetime`
  ORDER BY `datetime` DESC
  </code></pre>
  <p>The result-set has 130 rows although database holds 187, indicating the result includes some duplicates of <code>home</code>.</p>
  <p>Then I tried joining to a subquery that gets the maximum <code>datetime</code> for each row <code>id</code>:</p>
  <pre><code>-- 2 ..join
  
  SELECT
    s1.id,
    s1.home,
    s1.datetime,
    s1.player,
    s1.resource
  FROM TopTen s1
  JOIN (SELECT
    id,
    MAX(`datetime`) AS dt
  FROM TopTen
  GROUP BY id) AS s2
    ON s1.id = s2.id
  ORDER BY `datetime`
  </code></pre>
  <p>Nope. Gives all the records.</p>
  <p>I tried various exotic queries, each with various results, but nothing that got me any closer to solving this problem.</p>