需要按每个唯一ID的时间戳提取最新记录 [英] Need To Pull Most Recent Record By Timestamp Per Unique ID

问题描述

我要道歉，这是我关于stackoverflow的第一个问题...

I'm going to apologize up front, this is my first question on stackoverflow...

我正在尝试查询记录表，其中每行都有VehicleID，纬度，经度，时间戳和其他各个字段.我需要为每个VehicleID只提取最新的经度和纬度.

I am attempting to query a table of records where each row has a VehicleID, latitude, longitude, timestamp and various other fields. What I need is to only pull the most recent latitude and longitude for each VehicleID.

删除了唯一ID一词，因为我显然使用不正确.

edit: removed the term unique ID as apparently I was using it incorrectly.

推荐答案

如果唯一ID确实是唯一的，那么您将始终拥有最新的经度和纬度，因为该ID将随着每一行记录而变化.

If the Unique ID is truely unique, then you will always have the most recent latitude and longitude, because the ID will change with every singe row.

如果唯一ID是外键(或引用其他表中唯一ID的ID)，则应执行以下操作:

If the Unique ID is a Foreign Key (or an ID referencing a unique ID from a different table) you should do something like this:

SELECT latitude, longitude, unique_id
FROM table INNER JOIN
(SELECT unique_id, MAX(timestamp) AS timestamp
FROM table
GROUP BY unique_id)t2 ON table.timestamp = t2.timestamp
AND table.unique_id = t2.unique_id;

这篇关于需要按每个唯一ID的时间戳提取最新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！