对一列进行 SELECT DISTINCT，返回多个其他列 (SQL Server)

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

提问by mwalsher

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?

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

采纳答案by Hogan

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

回答by Howie

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 

回答by artdanil

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.

您有一个正确的联接，因此如果表中的设备序列号记录超过 1 个GPSReport，它将获取所有这些记录并连接到从SELECT DISTINCT device_serial FROM GPSReport.

回答by Charles Bretana

try:

尝试：

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

回答by Ray

How about something like this - since I couldn't run it, I expect my synatx is not perfect

这样的事情怎么样 - 因为我无法运行它，我希望我的 Synatx 并不完美

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

如果有多个相同的 device_serial 和 datetime，您可能需要修改 order by 子句以选择首选记录

回答by Bliek

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

回答by Sourav Kundu

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

这是最终结果，不要使用 distinct ，因为这是新查询，它对所有“ select * FROM tbl GROUP BY bandsupported”都有帮助。它的工作与不同的一个相同，并获取所有行

回答by mn128b

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

在 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

回答by Lucio Mollinedo

The following is for Postgresql 9+.

以下适用于 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.

这些答案都不适合我（但这是 Google 为我的搜索返回的第一个链接）。我只需要获取每组行的第一行，其中给定表达式的计算结果相等，而在不使用任何聚合的情况下删除其他行。

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

这个答案向我展示了如何做到这一点DISTINCT ON（这与仅仅不同DISTINCT）：

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

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

在这种情况下，只取第一个z。其余的zs 从集合中丢弃。

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.

请记住，由于没有GROUP BY，您不能在该查询中使用真正的聚合。

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

查看链接中的答案以获取更多选项。这是非常彻底的。