How to Fetch the Latest Row's PID for Each Unique RID in MySQL?

Fetching Latest Row per Group Using MAX() and GROUP BY

Consider a scenario where you have a MySQL table with columns such as timestamp, cost, rid, etc. Your objective is to retrieve the pid of the latest row for each unique rid.

You attempted this using the following query:

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

However, the returned pid values were not aligned with the maximum timestamps. This is because GROUP BY considers all columns in the SELECT clause, and it selects the first matching row for each group.

To rectify this, we need to identify the rid and the maximum timestamp for each group. We can do this using a subquery:

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

The result of this query will look like this:

rid | ts
-----|-----
1    | 2011-04-14 01:06:06
2    | 2011-04-14 01:05:41
3    | 2011-04-14 01:14:14

Now, we can join the original table with this subquery, using rid and timestamp, to retrieve the desired pid values:

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

The result of this query will provide you with the pid, cost, timestamp, and rid of the latest row for each unique rid.

The above is the detailed content of How to Fetch the Latest Row's PID for Each Unique RID in MySQL?. For more information, please follow other related articles on the PHP Chinese website!