mwalsher mwalsher - 2 months ago 19
SQL Question

SELECT DISTINCT on one column, return multiple other columns (SQL Server)

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
RIGHT JOIN
(SELECT DISTINCT device_serial FROM GPSReport) AS G2
ON G2.device_serial = G1.device_serial
ORDER BY G2.device_serial, G1.datetime DESC


This returns 50 rows, but is not returning a unique row for each device_serial. It returns all of the reports for the first device, then all of the reports for the second device, etc.

Is what I'm trying to do possible in one query?

Answer
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