CODEWITHSUNDEEP
sqlsql-serverduplicatesidentityrecord
pooja_cute
pooja_cute

Reputation: 246

Checking Duplicate Records in Table Where Id is Identity

In my table, ID is primary key field and identity column. I want to check duplicate records. (Certainly duplicate records do not have same ID)

And do not have date fields same.

How do I check this.

Extra Detail: I have 10 columns with 1 ID, 2 Date, and other 3 string, 3 Int, 1 bit.

Thansk in Advance.

Upvotes: 2

Views: 3449

Answers (3)

Vijay
Vijay

Reputation: 446

A generalized approach would be as follows. hope this helps you.

SELECT COL1, COL2, ..., COLn, COUNT(*) 
FROM TABLE1 
GROUP BY COL1,COL2, .., COLn 
HAVING COUNT(*) > 1

Upvotes: 0

iTSrAVIE
iTSrAVIE

Reputation: 834

Please use GROUP BY to group similar records and HAVING to specify condition on them.

select count(string1), count(string2),count(string3),count(int1), count(int2),count(int3),count(bit1)
from table1
group by string1, string2, string3, int1, int2, int3, bit1
having count(string1) > 1 and count(string2) > 1 and count(string3) > 1 and count(int1) > 1 and count(int2) > 1 and count(int3) > 1 and count(bit1) > 1

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171599

You can use GROUP BY to group similar records to count them, and then add a HAVING clause to filter out only those that occur more than once:

select StringCol1, StringCol2, StringCol3, IntCol1, IntCol2, IntCol3, BitCol1, count(*) as Count
from MyTable
group by StringCol1, StringCol2, StringCol3, IntCol1, IntCol2, IntCol3, BitCol1
having count(*) > 1

Upvotes: 4

Related Questions