CODEWITHSUNDEEP
sql-server-2008t-sql
Jack Hughes
Jack Hughes

Reputation: 5654

How do I find duplicate values in a SQL Server table?

My SQL Server 2008 database has a table called Reading with columns Timestamp a DateTime value and Probe_id which is a foreign key column to the Probe table.

My database has unfortunately got some duplicate values in the Reading table I wish to remove but I am struggling with the query to find them.

I thought something like this would find the duplicates:

select * from Reading where Probe_id = Probe_id and Timestamp = Timestamp;

Anybody any ideas?

Upvotes: 0

Views: 59

Answers (2)

Oleksandr Kucher
Oleksandr Kucher

Reputation: 489

WITH    Duplicates
      AS (SELECT    Probe_id,
                    Timestamp,
                    ROW_NUMBER() OVER (PARTITION BY Probe_id, Timestamp ORDER BY Probe_id, Timestamp) AS [RowNumber]
          FROM      Reading
         )
DELETE  FROM Duplicates
WHERE   Duplicates.RowNumber > 1

Upvotes: 3

juergen d
juergen d

Reputation: 204746

Group by the columns making the records duplicates and then count how much every group has

select Probe_id, Timestamp, count(*) as num_of_duplicates
from Reading 
group by Probe_id, Timestamp
having count(*) > 1

Upvotes: 2

Related Questions