How to Select the Latest Records for Each Method and ID Combination in MySQL?

How to Retrieve the Latest Dated Records from a MySQL Table

When storing time-sensitive data in a MySQL table, it's often necessary to retrieve the most recent records for specific combinations of fields. The following example demonstrates how to achieve this when dealing with a table of RPC responses featuring fields such as timestamps, methods, and IDs.

Question

Consider a table named rpc_responses with the following structure:

CREATE TABLE rpc_responses (
  timestamp DATE,
  method VARCHAR,
  id VARCHAR,
  response MEDIUMTEXT,
  PRIMARY KEY (timestamp, method, id)
);

Given the following 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

Obtain a list of the most recent responses for all existing combinations of method and id:

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

Answer

To accomplish this, utilize the following query:

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;

This query efficiently retrieves the desired result without the need for joins. It also maintains the desired row structure, providing a distinct record for each unique combination of method and id.

The above is the detailed content of How to Select the Latest Records for Each Method and ID Combination in MySQL?. For more information, please follow other related articles on the PHP Chinese website!