How to Get the Latest PID for Each RID in MySQL Using MAX() and GROUP BY?

MySQL Query: MAX() GROUP BY

Question:

How do you retrieve the PID of the latest row for each RID in a table using a MySQL query that combines MAX() and GROUP BY?

Sample Data:

CREATE TABLE theTable (
    `pid` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    `timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `cost` INT UNSIGNED NOT NULL,
    `rid` INT NOT NULL,
) Engine=InnoDB;

INSERT INTO theTable (`pid`, `timestamp`, `cost`, `rid`)
VALUES
  (1, '2011-04-14 01:05:07', 1122, 1),
  (2, '2011-04-14 00:05:07', 2233, 1),
  (3, '2011-04-14 01:05:41', 4455, 2),
  (4, '2011-04-14 01:01:11', 5566, 2),
  (5, '2011-04-14 01:06:06', 345, 1),
  (6, '2011-04-13 22:06:06', 543, 2),
  (7, '2011-04-14 01:14:14', 5435, 3),
  (8, '2011-04-14 01:10:13', 6767, 3)
;

Desired Result:

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

Incorrect Query:

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

This query would return the PID of the first occurrence of each RID, not the latest.

Correct Query:

To retrieve the PID of the latest row for each RID, you can use the following query:

SELECT pid, MAX(timestamp) AS latest_timestamp, rid
FROM theTable
GROUP BY rid
HAVING latest_timestamp = MAX(timestamp)

This query first uses the GROUP BY clause to group rows by the RID column. It then uses the MAX() function to calculate the maximum timestamp for each group of rows. Finally, it filters the results using the HAVING clause to keep only the rows with the latest timestamp for each RID.

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