How to Retrieve the PID of the Latest Row for Each Unique RID in MySQL Using MAX() and GROUP BY?

MySQL Query: MAX() Group By

In MySQL, the GROUP BY clause is used to combine rows with identical values in a specified group, while the MAX() function returns the maximum value in a given column within a group.

Problem Statement

Given a table with the following columns:

• pid (auto-increment primary key)
• timestamp (timestamp with default value)
• cost (integer)
• rid (integer)

The goal is to retrieve the PID of the latest row for each unique rid. The expected output should be:

pid	MAX(timestamp)	rid
5	2011-04-14 01:06:06	1
3	2011-04-14 01:05:41	2
7	2011-04-14 01:14:14	3

Initial Query

The following query was attempted to solve the problem:

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

However, this query returned incorrect PIDs, as it selected the PIDs of the first occurrences of rid values instead of the latest timestamps.

Solution Query

To obtain the desired results, the following query can be used:

SELECT pid, MAX(timestamp), rid
FROM (
    SELECT pid, timestamp, rid,
        ROW_NUMBER() OVER (PARTITION BY rid ORDER BY timestamp DESC) AS rn
    FROM theTable
) AS subquery
WHERE rn = 1
GROUP BY rid

Explanation

This query uses a subquery to rank the rows for each rid in descending order of timestamps using the ROW_NUMBER() function. The rn column assigns a rank to each row, where 1 represents the row with the latest timestamp for each rid.

The outer query then filters the results to only include rows with rn equal to 1, ensuring that only the latest row for each rid is selected. The pid, MAX(timestamp), and rid values are grouped by rid to produce the desired output.

The above is the detailed content of How to Retrieve the PID of the Latest Row for Each Unique RID in MySQL Using MAX() and GROUP BY?. For more information, please follow other related articles on the PHP Chinese website!