SQLTeam.com | Weblogs | Forums

Choosing duplicate records


#1

Hi all,

We had an issue with an application that double posted labor entries. Now I need to figure out how to use SQL to delete the duplicated records. There is a timestamp field in the table, so I need to create a statement that shows me the greater timestamp field where all of these fields are the same.

Hope that make sense.

Thanks!!


#2

Not sure if you want to keep the first or the last row, but in general here's how you do that efficiently:

;WITH cte_dups AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY col1, col2, col3, ... ORDER BY timestamp_column) AS row_num
    FROM table_name
)
DELETE
FROM cte_cups
WHERE row_num > 1