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

Question

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 1

You are having a right join, so if you have more than 1 record for device serial number in table GPSReport, it will get all those record and joint then to the unique list received from SELECT DISTINCT device_serial FROM GPSReport.

Answer 2

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

Answer 3

try:

   Select r.*   
   From GPSReport r
   Where datetime =
        (Select Max(DateTime)
         From GPSReport 
         Where device_serial = r.device_serial)

Answer 4

How about something like this - since I couldn't run it, I expect my synatx is not perfect

select *
  from (
    select device_serial, [datetime], triggerID, latitude, longitude, speed, [address],
        ROW_NUMBER() over (partition by device_serial order by device_serial asc, [datetime] desc) as row
      from gpsreport
  ) as data
  where row = 1

You may need to modify the order by clause to select the preferred record if there are multiples with the same device_serial and datetime

Answer 5

I would do it with a Common Table Expression (CTE), like so:

With ResultTable (RowNumber
                 ,device_serial
                 ,datetime
                 ,triggerID
                 ,latitude
                 ,longitude
                 ,speed
                 ,address)
AS
(
    SELECT Row_Number() OVER (PARTITION BY device_serial
                                  ORDER BY datetime DESC)
          ,device_serial
          ,datetime
          ,triggerID
          ,latitude
          ,longitude
          ,speed
          ,address
      FROM GPSReport
)
    SELECT device_serial
          ,datetime
          ,triggerID
          ,latitude
          ,longitude
          ,speed
          ,address
      FROM ResultTable
     WHERE RowNumber = 1