mysql query for most recent and unique ids

Question

I need some help with a mysql query. I have a table with columns entryID, nodeID, latitude, longitude, and timestamp. I want to retrieve the most recent lat/lon entry for each nodeID, i.e. I want to retrieve exactly one entry (the most recent) per unique nodeID.

If you down vote, say why. It would be great for everyone to know. — MikeEL, Dec 31 '13 at 14:50
possible duplicate for http://stackoverflow.com/questions/8523374/mysql-get-most-recent-record? — aksu, Dec 31 '13 at 15:02

score 0 · Accepted Answer · answered Dec 31 '13 at 14:45

0

You can do this:

SELECT t1.*
FROM tablename AS t1
INNER JOIN
(
   SELECT nodeID, MAX(timestamp) AS Latesttimestamp
   FROM tablename
   GROUP BY nodeID
) AS t2 ON t1.timestamp = t2.Latesttimestamp AND t1.nodeID = t2.nodeID

answered Dec 31 '13 at 14:45

Mahmoud Gamal

75,299
16
132
159

Thanks! This is exactly what I was looking for! – Gavman Dec 31 '13 at 15:03
@Gavman - You're welcome any time :) – Mahmoud Gamal Dec 31 '13 at 15:04

mysql query for most recent and unique ids

1 Answers1