I'm trying to write a query that returns the most recent GPS positions from a GPSReport table for each unique device. There are 50 devices in the table, so I only want 50 rows returned.
Here is what I have so far (not working)
SELECT TOP(SELECT COUNT(DISTINCT device_serial) FROM GPSReport) * FROM GPSReport AS G1
(SELECT DISTINCT device_serial FROM GPSReport) AS G2
ON G2.device_serial = G1.device_serial
ORDER BY G2.device_serial, G1.datetime DESC
SELECT * FROM GPSReport AS G1 JOIN (SELECT device_serial, max(datetime) as mostrecent FROM GPSReport group by device_serial) AS G2 ON G2.device_serial = G1.device_serial and g2.mostrecent = g1.datetime ORDER BY G1.device_serial