獲取每條記錄的單個最大日期

我有一個帶有 gps 數據表的設備列表。我想獲取查詢中每個設備收到的最後一個 gps 座標。

SELECT g.[TimeStamp], d.DeviceId, p.IsHistorical, g.Latitude, g.Longitude, g.Speed, g.Bearing, g.Fix, g.SatelliteCount, p.MessageTypeId
FROM Obd.Device d
LEFT JOIN obd.Packet p ON p.DeviceId = d.Id
LEFT JOIN obd.GPSData g ON g.PacketId = p.Id
WHERE d.ISDId = 1000 AND (g.Latitude IS NOT NULL OR g.Longitude IS NOT NULL) 

第一部分為每個匹配條件的設備選擇 MAX(TimeStamp)，使用 INNER JOIN 選擇記錄 WHERE DeviceID 和 MAX(TimeStamp) 相同。

WITH MDEV AS
(
   SELECT MAX(g.[TimeStamp]) MTS, d.DeviceId DevID
   FROM Obd.Device d
        LEFT JOIN obd.Packet p ON p.DeviceId = d.Id
        LEFT JOIN obd.GPSData g ON g.PacketId = p.Id
   WHERE d.ISDId = 1000 AND (g.Latitude IS NOT NULL OR g.Longitude IS NOT NULL)
   GROUP BY d.DeviceId
)
   SELECT g.[TimeStamp], d.DeviceId, p.IsHistorical, g.Latitude, 
          g.Longitude, g.Speed, g.Bearing, g.Fix, g.SatelliteCount, p.MessageTypeId
   FROM Obd.Device d
        LEFT JOIN obd.Packet p ON p.DeviceId = d.Id
        LEFT JOIN obd.GPSData g ON g.PacketId = p.Id
        INNER JOIN MDEV
               ON MDEV.DevID = d.DeviceId
               AND MDEV.MTS = g.[TimeStamp] 
   WHERE d.ISDId = 1000 
         AND (g.Latitude IS NOT NULL OR g.Longitude IS NOT NULL);

引用自：https://dba.stackexchange.com/questions/164353