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 356 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

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),method,id FROM rpc_responses GROUP BY method,id) latest
USING (timestamp,method,id);

Answer 2

The concept of "most recent" is fairly vague. If you mean something like the 100 most recent rows then you can just add a TOP(100) to your SELECT clause.

If you mean the "most recent" based on a the most recent date then you can just do

SELECT timestamp,method,id,response 
FROM rpc_responses
HAVING max(timestamp) = timestamp

Answer 3

...is more than one year later but i might help someone To select all the queries starting from latest

SELECT * FROM rpc_responses ORDER BY timestamp DESC

Answer 4

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.type = o2.type ) ORDER BY o1.timestamp, o1.method, o1.response