title: SELECT DISTINCT on one column, return multiple other columns (SQL Server)
body: <p>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.</p>
  
  <p>Here is what I have so far (not working)</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>Is what I'm trying to do possible in one query?</p>