How to Retrieve the Most Recent Row for a Given ID in SQL?

Retrieving the Most Recent Row for a Given ID

In a situation where a database table contains multiple rows for the same ID, it may be necessary to extract only the most recent one. This article addresses how to achieve this in SQL.

Using the provided table as an example:

+----+---------------------+---------+
| 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    |
+----+---------------------+---------+

Aggregate MAX(signin) Grouped by ID

To obtain the most recent signin time for each ID, group the rows by ID and aggregate with MAX(signin). This will provide a list of IDs with their corresponding latest signin times.

SQL Query:

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

INNER JOIN Against a Subquery

To retrieve the entire most recent row for a specific ID (e.g., ID=1), use an INNER JOIN against a subquery that calculates the MAX(signin) per ID. Then, filter the results based on the desired ID.

SQL Query:

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 Given ID in SQL?. For more information, please follow other related articles on the PHP Chinese website!