HyperDevil HyperDevil - 3 months ago 12
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

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
FROM ais_cnb a INNER JOIN
(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))
Comments