在一个列上选择DISTINCT，返回多个其他列(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.

我正在编写一个查询，它返回每个唯一设备的GPSReport表中最近的GPS位置。表中有50个设备，所以我只希望返回50行。

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.

这将返回50行，但不会为每个device_serial返回唯一的行。它返回第一个设备的所有报告，然后返回第二个设备的所有报告，等等。

Is what I'm trying to do possible in one query?

我尝试在一个查询中做的事情可能吗?

8 个解决方案

#1

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

#2

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

#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.

您有一个正确的连接，所以如果您在表GPSReport中有一个以上的设备序列号记录，那么它将获得所有这些记录并连接到从GPSReport中SELECT DISTINCT device_serial接收到的唯一列表。

#4

try:

试一试:

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

#5

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

您可能需要修改order by子句来选择首选记录，如果有相同的device_series和datetime的倍数。

#6

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

我将使用一个公共表表达式(CTE)，如下所示:

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

#7

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

这是最后的结果，不使用不同的，因为这是新的查询，它对所有“select * FROM tbl GROUP BY bandsupport”都有帮助。它的工作与不同的一个领域和得到所有的行是一样的

#8

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

在*上尝试了所有可能的答案后，我发现了这个惊人的结果

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

#1

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

#2

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

#3

#4