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

WITH DEDUPE AS (
    SELECT  *
          , ROW_NUMBER() OVER ( PARTITION BY what_you_want_for_distinct ORDER BY what_you_want_for_distinct) AS OCCURENCE
    FROM tablename
    )
SELECT  * FROM DEDUPE
WHERE
OCCURENCE = 1 

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

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.