Get data from last 30 minutes and get the latest rows

Question

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 1

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

Answer 2

Well, there is a syntactic error there. As I explained here some time ago, you can't reference a calculated field in a where clause, so the timestamp you're getting is actually the field, not the aggregated function (the max()). You didn't realised that because you named it the same way as the field. Try running this and you'll see:

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

Now, regardless of whether you're properly selecting those records or not, if you first get all the latest 30 minutes data, and then you get just the newest data from that subset... isn't it the same as getting the newest data?

Additionally it might be a good idea to add all the other fields to the group by.

Or maybe I'm getting something wrong. Can you please elaborate on that?

Edit: To filter the grouped data you need to add a HAVING clause. Your previous query should be written the following way (but I'm not sure if that is what you're looking for):

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

I do think, however, that you might be looking for the greatest max of each group, which should be solved with a different query... But as I said, I don't have enough info about the problem to conclude that.