Sql-server – Get Single greatest date for each record

greatest-n-per-groupsql serversql-server-2012

I have a list of Devices with a gps data table. I would like to grab the last gps coordinates received for each device in the query.

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)

enter image description here

Best Answer

First part selects MAX(TimeStamp) for every device that match the conditions, the use a INNER JOIN to select records WHERE DeviceID and MAX(TimeStamp) are the same.

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);
Related Question