How to Efficiently Retrieve the Latest `pid` for Each Unique `rid` Using MySQL's `MAX()` and `GROUP BY`?

MySQL Query Optimization: MAX() and GROUP BY

Problem:

Given a table with columns pid, timestamp, cost, and rid, the task is to retrieve the pid of the latest row for each unique rid, considering that pid is auto-increment primary key.

Original Query:

The following query retrieves the maximum timestamp and corresponding rid values, but it doesn't correctly return the latest pid:

SELECT MAX(timestamp), rid, pid FROM theTable GROUP BY rid

Solution:

To effectively get the latest pid for each rid, a combination of techniques is required:

1. Identify the maximum timestamps and corresponding rid values:

   SELECT rid, MAX(timestamp) AS maxt FROM theTable GROUP BY rid

2. Join the original table against the maximum timestamp results:

   SELECT
       t.pid,
       t.cost,
       t.timestamp,
       t.rid
   FROM
       theTable AS t
   JOIN
       (SELECT rid, MAX(timestamp) AS maxt FROM theTable GROUP BY rid) AS maxt
   ON
       t.rid = maxt.rid AND t.timestamp = maxt.maxt

This revised query effectively retrieves the pid, cost, timestamp, and rid of the latest row for each unique rid in the original table.

The above is the detailed content of How to Efficiently Retrieve the Latest `pid` for Each Unique `rid` Using MySQL's `MAX()` and `GROUP BY`?. For more information, please follow other related articles on the PHP Chinese website!