title: MySQL query, MAX() + GROUP BY
body: <p>Daft SQL question. I have a table like so ('pid' is auto-increment primary col)</p>
  <pre><code>CREATE TABLE theTable (
      `pid` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
      `timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      `cost` INT UNSIGNED NOT NULL,
      `rid` INT NOT NULL,
  ) Engine=InnoDB;
  </code></pre>
  <p>Actual table data:</p>
  <pre><code>INSERT INTO theTable (`pid`, `timestamp`, `cost`, `rid`)
  VALUES
    (1, '2011-04-14 01:05:07', 1122, 1),
    (2, '2011-04-14 00:05:07', 2233, 1),
    (3, '2011-04-14 01:05:41', 4455, 2),
    (4, '2011-04-14 01:01:11', 5566, 2),
    (5, '2011-04-14 01:06:06', 345, 1),
    (6, '2011-04-13 22:06:06', 543, 2),
    (7, '2011-04-14 01:14:14', 5435, 3),
    (8, '2011-04-14 01:10:13', 6767, 3)
  ;
  </code></pre>
  <p>I want to get the PID of the latest row for each rid (1 result per unique RID). For the sample data, I'd like:</p>
  <pre><code>pid | MAX(timestamp)      | rid
  -----------------------------------
  5   | 2011-04-14 01:06:06 | 1
  3   | 2011-04-14 01:05:41 | 2
  7   | 2011-04-14 01:14:14 | 3
  </code></pre>
  <p>I've tried running the following query:</p>
  <pre><code>SELECT MAX(timestamp),rid,pid FROM theTable GROUP BY rid
  </code></pre>
  <p>and I get:</p>
  <pre><code>max(timestamp)     ; rid; pid
  ----------------------------
  2011-04-14 01:06:06; 1  ; 1
  2011-04-14 01:05:41; 2  ; 3
  2011-04-14 01:14:14; 3  ; 7
  </code></pre>
  <p>The PID returned is always the first occurence of PID for an RID (row / pid 1 is frst time rid 1 is used, row / pid 3 the first time RID 2 is used, row / pid 7 is first time rid 3 is used). Though returning the max timestamp for each rid, the pids are not the pids for the timestamps from the original table. What query would give me the results I'm looking for?</p>