Stupid MySQL query question
-
This is a query scenario that's often come up but I've never quite figured out the best way to handle it. Let's say there's a table that records events from a set of 500 servers. The schema looks something like:
id int primary key,
ts timestamp,
server_id int,
event_id int,
event_msg varchar(255),
key (server_id, ts)Is there an easy way to query for the latest events on each server? Short of having another table which just records the latest events per server, is there a way to do this efficiently? The schema is somewhat flexible and keys can be deleted or added.
-
Without knowing the version of MySQL nor when derived tables were supported, I'd say this would work with 5.0 or later. It is possible that it would return multiple records per server unless (server_id, ts) is a unique key. Having that as a unique key may cause problems depending on datetime resolution and frequency of events.
SELECT main.id, main.ts, main.server_id, main.event_id, main.event_msg FROM MyTable AS main INNER JOIN (SELECT max(ts) as maxts, server_id FROM MyTable GROUP BY server_id) as latest ON main.ts = latest.maxts AND main.server_id = latest.server_id
-
Ah, thank you! I can't believe I hadn't thought of that before. Especially since I've been playing with derived tables quite a bit recently.
