ansaurus

Question

Remove all but one duplicate rows where datetime column values are within seconds of each other?

Answer 1

A:

group by all the fields except the timestamp and take the max(timestamp_field) value?

Beth 2010-09-17 19:07:58

Answer 2

A:

Here's how you can get one row from each group of records that are grouped by the 30-second range. This query can be used to see which rows you would keep in the table.

WITH cte AS
    ( SELECT UserID, ActorID, ActorType, BoxID, Query, CreateDate,
        DATEDIFF(ss, '1/1/2000', CreateDate) / 30 AS CreateDateGroup,
        ROW_NUMBER() OVER (PARTITION BY UserID, ActorID, ActorType, BoxID, Query,
                                     DATEDIFF(ss, '1/1/2000', CreateDate) / 30
                           ORDER BY CreateDate ASC) AS sequence
    FROM TrackingLog
    )

SELECT UserID, ActorID, ActorType, BoxID, Query, CreateDate, CreateDateGroup, sequence
FROM cte
WHERE sequence = 1

Two columns are produced in the common table expression (CTE). The CreateDateGroup column is calculated by converting the CreateDate value to the number of seconds since '1/1/2000', and divided by 30 (as in seconds). The result is an integer, so the fractional part is truncated.

The sequence column is the row number within the group and is ordered by CreateDate, in ascending order. So, the oldest date in each group will be sequence 1.

The main query includes WHERE sequence = 1, which indicates you want to see the first row in each group.

When you are ready to delete the unwanted rows, you would alter the main query like the following:

WITH cte AS
    ( SELECT UserID, ActorID, ActorType, BoxID, Query, CreateDate,
        DATEDIFF(ss, '1/1/2000', CreateDate) / 30 AS CreateDateGroup,
        ROW_NUMBER() OVER (PARTITION BY UserID, ActorID, ActorType, BoxID, Query,
                                     DATEDIFF(ss, '1/1/2000', CreateDate) / 30
                           ORDER BY CreateDate ASC) AS sequence
    FROM TrackingLog
    )

DELETE
FROM cte
WHERE sequence > 1
;

This command will delete all rows from the table that are not the first row of each group.

bobs 2010-09-17 22:10:21

ansaurus

tags:

views:

answers:

Remove all but one duplicate rows where datetime column values are within seconds of each other?

related questions