MySQL query, MAX() + GROUP BY

Question

Daft SQL question. I have a table like so ('pid' is auto-increment primary col)

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;

Actual table data:

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)
;

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:

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

I've tried running the following query:

SELECT MAX(timestamp),rid,pid FROM theTable GROUP BY rid

and I get:

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

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?

Answer 1

(Tested in PostgreSQL 9.something)

Identify the rid and timestamp.

select rid, max(timestamp) as ts
from test
group by rid;

1   2011-04-14 18:46:00
2   2011-04-14 14:59:00

Join to it.

select test.pid, test.cost, test.timestamp, test.rid
from test
inner join 
    (select rid, max(timestamp) as ts
    from test
    group by rid) maxt
on (test.rid = maxt.rid and test.timestamp = maxt.ts)