sql-server - 当键都不同时，如何获取最近的行？

Question

我知道之前有人问过这个问题，但我似乎找不到合适的解决方案。

我有这个数据:

Label           StartDate     ActivityKey
------------------------------------------------------------------
LABELS          2009-02-12    23D645CA-7F05-47FF-9AC4-1414DCBF44DD
LABELS          2010-11-01    C266A254-2A3D-4A37-8281-AE9EA08ED086
MASTER BOXES    2009-02-12    81DBEA52-B7BC-4861-96B4-7A77A2D7F07B
MASTER BOXES    2010-11-01    9DAD9F77-46FD-4694-9168-E4E5FE306B7D

我只想要每个 Label 的最新信息。这是需要的:

Label           StartDate     ActivityKey
------------------------------------------------------------------
LABELS          2010-11-01    C266A254-2A3D-4A37-8281-AE9EA08ED086
MASTER BOXES    2010-11-01    9DAD9F77-46FD-4694-9168-E4E5FE306B7D

感谢任何提示

(顺便说一句，这是 SQL Server 2008)

Answer 1

SELECT label, StartDate, ActivityKey
    FROM (SELECT label, StartDate, ActivityKey,
                 ROW_NUMBER() OVER (PARTITION BY label ORDER BY StartDate DESC) AS RowNum
              FROM YourTable
         ) t
    WHERE t.RowNum = 1

同样的查询也可以用 CTE 来完成。 :

WITH cteRowNum AS (
    SELECT label, StartDate, ActivityKey,
           ROW_NUMBER() OVER (PARTITION BY label ORDER BY StartDate DESC) AS RowNum
        FROM YourTable
)
SELECT label, StartDate, ActivityKey
    FROM cteRowNum
    WHERE RowNum = 1