如果值相等，则 SQL Max 返回重复项

Question

我有一个 View ，其中包含一个文档 ID 列和一个日期列，以及许多与此问题无关的其他列。可以有多个行具有相同的文档 ID，但日期通常不同。这表示它是同一份文件，只是它的修订版。问题是，如果我有两行文档 ID 和日期相同，我会得到两者。我只想得到一个。哪一个都无所谓，只要我能得到一个。

以下内容重复，文档 ID 和日期相同。

SELECT FSD.*
FROM vFSD FSD
INNER JOIN
    (
    SELECT InternalID, MAX(FileLastUploadedDate) AS FileLastUploadedDate
    FROM vFSD
    GROUP BY InternalID
    ) gFSD ON FSD.InternalID = gFSD.InternalID AND FSD.FileLastUploadedDate = gFSD.FileLastUploadedDate

我也用 DISTINCT 尝试过，但没有解决问题。

SELECT DISTINCT FSD.*
FROM vFSD FSD
INNER JOIN
    (
    SELECT DISTINCT InternalID, MAX(FileLastUploadedDate) AS FileLastUploadedDate
    FROM vFSD
    GROUP BY InternalID
    ) gFSD ON FSD.InternalID = gFSD.InternalID AND FSD.FileLastUploadedDate = gFSD.FileLastUploadedDate

Answer 1

您可以使用 ROW_NUMBER 只返回任意一行，如果两行与 InternalID 的相同最大 FileLastUploadedDate 相关联

WITH CTE
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY InternalID 
                                       ORDER BY FileLastUploadedDate DESC) AS RN
         FROM   vFSD)
SELECT InternalID,
       FileLastUploadedDate
       /*Other desired columns*/ 
FROM   CTE
WHERE  RN = 1