How to Efficiently Retrieve the Most Recent Records from a Dated MySQL Table?

Retrieving the Most Recent Records from a Dated MySQL Table

To select the most recent responses for all existing combinations of method and id in a MySQL table with the structure:

Table: rpc_responses

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

PRIMARY KEY(timestamp,method,id)

Consider the following steps:

1. Create a Subquery to Select Groupings:

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

   • The @last_method variable is initialized to an empty string.
   • The if() statement creates a new_method_group column that indicates the start of a new group of rows with the same method value.

2. Filter for the First Row in Each Group:

   WHERE new_method_group=1

   This condition ensures that only the most recent row for each combination of method and timestamp is selected.

3. Select the Desired Columns:

   SELECT timestamp, method, id, response
   FROM t1

This query will return the desired result set, which includes the most recent responses for all existing combinations of method and id. It is efficient because it avoids using joins and leverages MySQL variables to track the transition between different method groups.

The above is the detailed content of How to Efficiently Retrieve the Most Recent Records from a Dated MySQL Table?. For more information, please follow other related articles on the PHP Chinese website!