How to Efficiently Retrieve the Most Recent Records for Each Method and ID in MySQL and PostgreSQL?

Retrieving the Most Recent Records from a MySQL Table

Problem:

A MySQL table stores the responses to various RPC calls with fields for timestamp, method, ID, and response. The goal is to retrieve the most recent responses for all combinations of method and ID.

Assumptions:

• For each date, there can be one response for a given method/ID.
• Not all call combinations are necessarily present for a given date.
• There are numerous methods, thousands of IDs, and at least 365 different dates.

Solution:

The following query can be used to efficiently retrieve the desired result by utilizing MySQL variables to avoid a JOIN:

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 first assigns a new_method_group value to each row, which indicates the start of a new method group. It then filters the results to include only rows with a new_method_group value of 1, ensuring that only the most recent row for each method is included in the final result.

Alternative Solution for PostgreSQL:

PostgreSQL provides a built-in feature for this task:

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

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