HyperDevil HyperDevil - 1 year ago 77
MySQL Question

Get data from last 30 minutes and get the latest rows

I have a table with AIS marine data that gets updated very often.

What I need is the data from the last 30 minutes and from that result the newest rows and MMSI should be unique.

The query I have now:

select max(timestamp) as timestamp, mmsi, navstatus, rot, sog, lon, lat, cog,
thead, man, mtype from ais_cnb
where (timestamp > (now() - interval 30 minute))
group by mmsi
order by timestamp desc

It seems like all the data except the timestamp is old.

Answer Source

If you are wanting the latest row from the last 30 minutes for each unique "mmsi" that has one, then using a join to a subquery where you find the max timestamps first should work, like:

SELECT timestamp, a.mmsi, navstatus, rot, sog, lon, lat, cog, thead, man, mtype
(SELECT mmsi, MAX(timestamp) AS max_timestamp FROM ais_cnb
 WHERE timestamp > (now() - interval 30 minute)
 GROUP BY mmsi) t
ON ((timestamp = t.max_timestamp) AND (a.mmsi = t.mmsi))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download