sam.dahl (10/11/2012)

---

My understanding is that there's no way to use *, instead generate a list of columns and use that.

Modifying the CTE requires recognising how the ROW_NUMBER/PARTITION BY works.

Your code:

WITH DUPLICATES AS

(SELECT ROW_RUMBER() OVER (PARTITION BY ACTIVITYGROUP_DESC, REQUEST_Id, PullDate

ORDER BY ACTIVITYGROUP_DESC, REQUEST_Id, PullDate) AS Dups

FROM ActivityGroup

The ROW_NUMBER() function provides an incrementing number starting at 1 for each row. PARTITION BY operates on the table as sort of an on-the-fly grouping operation such that the ROW_NUMBER() is applied to each group (i.e. restarts at 1 for each partitioned section). In your context the fields you partition on are the fields being compared to determine whether or not the record constitutes a duplicate. The ORDER BY then sorts the grouped records in some particular order. If the fields that you order on are the same as the ones you partition by then the resulting intra-group order of 'duplicates' will be effectively arbitrary.

Using your example of comparing ACTIVITYGROUP_DESC and REQUEST_ID and removing the older PullDate something like this would work:

WITH DUPLICATES AS

(SELECT ROW_RUMBER() OVER (PARTITION BY ACTIVITYGROUP_DESC, REQUEST_ID --effectively group by these fields

ORDER BY PullDate DESC -- Sort by PullDate in descending order since you want the newest PullDate to be assigned value 1

) AS Dups

FROM ActivityGroup

Any rows with a value greater than one are older duplicates and can be deleted.

Does that help? (sometimes the explanations are clearer in my head before the attempt to articulate them).

Minor disclaimer: I haven't tested the code yet!