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) 

Answer 2

select * from (     select `pid`, `timestamp`, `cost`, `rid`     from theTable      order by `timestamp` desc ) as mynewtable group by mynewtable.`rid` order by mynewtable.`timestamp` 

Hope I helped !