Reputation: 246
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
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
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
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