CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
mwalsher
mwalsher

Reputation: 2790

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?

Upvotes: 23

Views: 121502

Answers (9)

Lucio Mollinedo
Lucio Mollinedo

Reputation: 2424

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.

Upvotes: 0

zinger
zinger

Reputation: 319

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

Upvotes: 1

Sourav Kundu
Sourav Kundu

Reputation: 24

This is the final result do not use distinct , for this is the new query, its helpfull for all "select * FROM tbl GROUP BY bandsupported" . its work same as distinct one filed and get all rows

Upvotes: 0

Howie
Howie

Reputation: 371

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

Upvotes: 37

Bliek
Bliek

Reputation: 466

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

Upvotes: 1

Ray
Ray

Reputation: 21905

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

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146419

try:

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

Upvotes: 1

Hogan
Hogan

Reputation: 70513

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

Upvotes: 19

artdanil
artdanil

Reputation: 5082

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.

Upvotes: 2

Related Questions