How to select the most recent set of dated records from a mysql table

Question

I am storing the response to various rpc calls in a mysql table with the following fields:

Table: rpc_responses

timestamp   (date)
method      (varchar)
id          (varchar)
response    (mediumtext)

PRIMARY KEY(timestamp,method,id)

What is the best method of selecting the most recent responses for all existing combinations of method and id?

• For each date there can only be one response for a given method/id.

• Not all call combinations are necessarily present for a given date.

• There are dozens of methods, thousands of ids and at least 365 different dates

Sample data:

timestamp  method  id response
2009-01-10 getThud 16 "....."
2009-01-10 getFoo  12 "....."
2009-01-10 getBar  12 "....."
2009-01-11 getFoo  12 "....."
2009-01-11 getBar  16 "....."

Desired result:

2009-01-10 getThud 16 "....."
2009-01-10 getBar 12 "....."
2009-01-11 getFoo 12 "....."
2009-01-11 getBar 16 "....."

(I don't think this is the same question - it won't give me the most recent response)

Answer 1

This solution was updated recently.
Comments below may be outdated

This can query may perform well, because there are no joins.

SELECT * FROM (     SELECT *,if(@last_method=method,0,1) as new_method_group,@last_method:=method      FROM rpc_responses      ORDER BY method,timestamp DESC ) as t1 WHERE new_method_group=1; 

Given that you want one resulting row per method this solution should work, using mysql variables to avoid a JOIN.

FYI, PostgreSQL has a way of doing this built into the language:

SELECT DISTINCT ON (method) timestamp, method, id, response FROM rpc_responses WHERE 1 # some where clause here ORDER BY method, timestamp DESC 

Answer 2

Self answered, but I'm not sure that it will be an efficient enough solution as the table grows:

SELECT timestamp,method,id,response FROM rpc_responses 
INNER JOIN
(SELECT max(timestamp) as timestamp,method,id FROM rpc_responses GROUP BY method,id) latest
USING (timestamp,method,id);

Answer 3

Try this...

SELECT o1.id, o1.timestamp, o1.method, o1.response   
FROM rpc_responses o1
WHERE o1.timestamp = ( SELECT max(o2.timestamp)
                       FROM rpc_responses o2
                       WHERE o1.id = o2.id )
ORDER BY o1.timestamp, o1.method, o1.response

...it even works in Access!