删除除两个列的最新唯一组合之外的所有

Question

我有一个表变量，需要从其中删除行。此查询显示表的内容：

SELECT
    useractionId,
    userId,
    actionId,
    ROW_NUMBER() OVER (PARTITION BY userid, actionId ORDER BY dateOfApplication ASC) AS applicationOrder, -- put into order of application (earliest first)
    dateOfApplication
FROM
    @tbl_appliedhistory;

结果如下，并提供每次用户完成操作的记录：

编辑-在只有一个userid/actionid组合的末尾添加了额外的行

useractionId userId     actionId    applicationOrder     dateOfApplication
----------- ----------- ----------- -------------------- -----------------------
7665        3           2479        1                    2013-09-08 18:17:23.953
7675        3           2479        2                    2013-09-08 18:18:56.063
7685        3           2479        3                    2013-09-08 18:21:21.487
8286        3           2695        1                    2013-11-07 09:28:03.817
8287        3           2695        2                    2013-11-11 13:47:11.337
8289        3           2695        3                    2013-11-11 13:47:56.390
8291        3           2695        4                    2013-11-11 13:48:11.957
8284        87453       2695        1                    2013-11-06 15:08:40.823
8285        87453       2695        2                    2013-11-06 15:09:26.167
8288        87453       2695        3                    2013-11-11 13:47:11.337
8290        87453       2695        4                    2013-11-11 13:47:56.390
8291        89000       3000        1                    2013-11-12 15:50:50.000

对于userId/actionId列值的每个组合，我需要删除除最高值以外的所有applicationOrder值(该特定actionId的最新完成操作)。因此，一旦删除，剩下的行将是：

useractionId  userId    actionId    applicationOrder     dateOfApplication
----------- ----------- ----------- -------------------- -----------------------
7685        3           2479        3                    2013-09-08 18:21:21.487
8291        3           2695        4                    2013-11-11 13:48:11.957
8290        87453       2695        4                    2013-11-11 13:47:56.390
8291        89000       3000        1                    2013-11-12 15:50:50.000

这些结果显示了用户完成每个操作的最新日期。如果您能帮我识别这些行，我将不胜感激。

Answer 1

在下面的示例中，与dateOfApplication的ORDER不同，ASC使用了dateOfApplication DESC的ORDER。

;WITH cte AS
 (SELECT ROW_NUMBER() OVER (PARTITION BY userid, actionId ORDER BY dateOfApplication DESC) AS applicationOrder
  FROM Table1
  )
DELETE cte
WHERE applicationOrder != 1

基于SQLFIddle的演示

Answer 2

您不能直接按dateOfApplication desc订购，然后删除row_number >1的所有内容吗？

如下所示：

;WITH CTE AS 
(
     SELECT
         useractionId,
         userId,
         actionId,
         ROW_NUMBER() OVER (PARTITION BY userid, actionId ORDER BY dateOfApplication DESC) AS applicationOrder, -- put into order of application (earliest first)
         dateOfApplication
     FROM @tbl_appliedhistory
)

DELETE FROM CTE WHERE applicationOrder > 1