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
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))