The most recent rows in for each type/agent

Question

I have a table (still on MySQL 5.7) with lots of data. This table is written by some scripts. It works like a "checkin", where each agent is checking in in some period of time. It looks like below.

id	agent	status	timestamp
1	tom	ok	2023-03-16 12:27:03
2	jeff	degraded	2023-08-31 00:01:13
100	tom	ok	2023-10-03 12:00:00
101	jeff	ok	2023-10-03 11:59:00

I'd like to pick the most fresh line per each agent. So in above example I'd like to get row 100 and 101.

I use following script, but it turns out only first two rows. I tried changing order by id or timestamp ASC/DESC.

SELECT * FROM (SELECT * FROM db_table ORDER BY timestamp ASC) as agnt GROUP BY agent

How to make it working?

Answer 1

There's a multitude of ways to solve this kind of problem. I typically use a window function like ROW_NUMBER() to enumerate the rows by a specific sort criteria. Then you can select only the rows you want using that enumeration.

Unfortunately in MySQL 5.7 and older, window functions don't exist, so an alternative solution is using a correlated subquery like so:

SELECT
    id,
    agent,
    status,
    timestamp
FROM checkin AS C1
WHERE timestamp =
(
    SELECT MAX(timestamp) AS LatestTimestamp
    FROM checkin AS C2
    WHERE C1.agent = C2.agent -- Join on the same agent
)

Note that this only works if the timestamp isn't duplicated for the same agent in the table.