0

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?

0

1 Answer 1

0

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.

6
  • Thanks for posting. I'm not sure if this is version of MySQL Workbench which doesn't support this kind window function (it has problem with "WITH").
    – Marek
    Oct 3, 2023 at 13:53
  • @Marek Ah, I missed your MySQL version. It's because 5.7 doesn't support CTEs. I just updated my answer to use a subquery for the window function instead, which should work for you.
    – J.D.
    Oct 3, 2023 at 13:55
  • Now I tried and still some incompatibility in this line "ROW_NUMBER() OVER (PARTITION BY agent ORDER BY timestamp DESC, id DESC) AS SortId". MySQL Workbench suggest "(" before partition is not expected there.
    – Marek
    Oct 3, 2023 at 15:57
  • @Marek ah interesting, I guess window functions also aren't supported in that old version of MySQL. I've updated my answer once more with an alternative solution.
    – J.D.
    Oct 3, 2023 at 20:02
  • Now it worked, thanks a lot!
    – Marek
    Oct 4, 2023 at 6:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.