How to Retrieve the Most Recent Row for a Specific ID in a Table? 或 How Can I Select Only the Latest Row with a Given ID from a Database Table?

Retrieve the Most Recent Row with a Specific ID

Consider a table containing three rows with the same ID (1) but different signin timestamps. The objective is to retrieve only the most recent row among them.

One approach involves using aggregate functions and grouping. By leveraging the MAX() function to find the maximum signin timestamp for each ID, you can effectively identify the most recent row. Group the results by the ID column to obtain the maximum signin timestamps for each unique ID.

SELECT 
 id, 
 MAX(signin) AS most_recent_signin
FROM tbl
GROUP BY id

To obtain the entire corresponding record, perform an INNER JOIN between the original table and a subquery that returns the maximum signin timestamp for each ID. Link the two tables on the ID column and filter based on the maximum signin timestamp. This will yield the most recent row for the specified ID.

For example, to retrieve the most recent row for ID=1:

SELECT 
  tbl.id,
  signin,
  signout
FROM tbl
  INNER JOIN (
    SELECT id, MAX(signin) AS maxsign FROM tbl GROUP BY id
  ) ms ON tbl.id = ms.id AND signin = maxsign
WHERE tbl.id=1

The above is the detailed content of How to Retrieve the Most Recent Row for a Specific ID in a Table? 或 How Can I Select Only the Latest Row with a Given ID from a Database Table?. For more information, please follow other related articles on the PHP Chinese website!