SQLTeam.com | Weblogs | Forums

Choosing duplicate records


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.



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
FROM cte_cups
WHERE row_num > 1