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

Use this solution with caution:
it is not guaranteed to work in future versions of mysql
it is not known to work in mariadb 5.5

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

SELECT * FROM (
    SELECT timestamp, method, id, response
    FROM rpc_responses
    WHERE 1 # some where clause here
    ORDER BY timestamp DESC
) as t1
GROUP BY method

The "group by", collapses the result set on method, and returns only 1 row per method, the most recent one, because of the ORDER BY timestamp DESC in the inner query.

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