1

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

1

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);
  • Is MDEV supposed to be a temple table or something ? – Jesse Feb 14 '17 at 23:05
  • It's a CTE (Common table expression). Which database are you working on? – McNets Feb 14 '17 at 23:09
  • it's similar to this: rextester.com/SDBMU66720 – McNets Feb 14 '17 at 23:23
  • 1
    Have found myself using CTE extensively now - Great answer! – Jesse Mar 13 '17 at 21:59
3

Another way is to use window functions, either ROW_NUMBER() or MIN():

WITH
  ct AS
  ( SELECT 
        g.[TimeStamp], d.DeviceId, p.IsHistorical, 
        g.Latitude, g.Longitude, g.Speed, g.Bearing, 
        g.Fix, g.SatelliteCount, p.MessageTypeId,
        rn = ROW_NUMBER() OVER (PARTITION BY d.DeviceID
                                ORDER BY g.[TimeStamp] DESC)
    FROM Obd.Device d
      JOIN obd.Packet p ON p.DeviceId = d.Id
      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)
  ) 
SELECT 
    [TimeStamp], DeviceId, IsHistorical, 
    Latitude, Longitude, Speed, Bearing, 
    Fix, SatelliteCount, MessageTypeId
FROM ct
WHERE rn = 1 ;

Irrelevant to the question but the condition on g.Latitude and g.Longitude makes the LEFT joins equivalent to INNER joins.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.