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

user_146048 0
Viewed 108730

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?

sql-server-2005 tsql sql-server sql
9 Answers

I found this amazing result after trying every possible answer on StackOverFlow

WITH cte AS /* Declaring a new table named 'cte' to be a clone of your table */
(SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY val1 DESC) AS rn
    FROM MyTable /* Selecting only unique values based on the "id" field */
)
SELECT * /* Here you can specify several columns to retrieve */
FROM cte
WHERE rn = 1
user_35287740

The following is for Postgresql 9+.

None of these answers worked for me (yet this was the first link returned by Google for my search). I needed to get only the first row of each set of rows where the given expressions evaluate to equal while dropping the other rows without using any aggregation.

This answer showed me how to do it with DISTINCT ON (which is different than just DISTINCT):

SELECT DISTINCT ON(x,y) z, k, r, t, v
FROM foo;

In that case, only the first z is taken. The rest of the zs are discarded from the set.

You can select just one column (which is what I did), instead of two like in the example.

Bear in mind that since there is no GROUP BY, you cannot use real aggregation in that query.

Check out the answer from the link for more options. It is very thorough.

user_27302330
Related