How to Efficiently Retrieve the Latest Row for a Given ID Based on Timestamps?

Retrieving the Latest Row for a Specified ID

In a table containing multiple rows with the same ID, how can you efficiently extract only the most recent row based on timestamps? Consider the example table:

| id | signin              | signout |
+----+---------------------+---------+
|  1 | 2011-12-12 09:27:24 | NULL    |
|  1 | 2011-12-13 09:27:31 | NULL    |
|  1 | 2011-12-14 09:27:34 | NULL    |
|  2 | 2011-12-14 09:28:21 | NULL    |
+----+---------------------+---------+

To achieve this, you can utilize aggregate functions and subqueries.

Using Aggregate MAX()

The aggregate MAX() function can be used to determine the latest signin timestamp for each ID. By grouping the results by the id column, you can identify the maximum signin timestamp for each unique ID.

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

Retrieving the Complete Row

To retrieve the entire row corresponding to the most recent signin for a specific ID, use an INNER JOIN against a subquery that returns the maximum signin timestamp per ID.

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

This query will return the complete most recent record with>

The above is the detailed content of How to Efficiently Retrieve the Latest Row for a Given ID Based on Timestamps?. For more information, please follow other related articles on the PHP Chinese website!