How to Retrieve the Most Recent Row for a Specific ID in a Database Table?

Retrieve Most Recent Row Based on ID

In a database table containing multiple rows for each ID, the task is to retrieve only the most recent row for a specified ID. Consider the table below:

+----+---------------------+---------+
| 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 obtain the most recent row with ID = 1 based on the 'signin' column, aggregate the 'signin' column using the MAX() function and group by ID:

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

This query returns the latest 'signin' timestamp for each ID.

To extract the complete row associated with the most recent 'signin', perform an INNER JOIN against a subquery that returns the MAX('signin') for each 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 retrieves the ID, 'signin', and 'signout' for the row with the most recent 'signin' for ID = 1.

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