Wednesday, February 08, 2006 4:27 PMI need a T-SQL query that will pull out records, that have certain values in common.
The goal is to find duplicate records in one of our tables.
Any help or suggestions would be appreciated.
Wednesday, February 08, 2006 5:06 PM
The general solution is to perform a select that groups by the columns and counts the occurrances. You are interested in the ones with counts greater than 1. That give you the values that are repeated. This result set can be used to find the actual offending records.
Suppose you suspect that multiple checks have been issued. You have a table that contains check information where the bank account and the check number identify a check.
From paychecks p
occurs = count(*)
Having count(*) > 1
Where p.account = dups.account
And p.check_no = dups.check_no
Thursday, February 09, 2006 9:36 AMselect p1.keyfield1 = p1.keyfieldN
from paycheck p1 left join paycheck p2
on p1.keyfield1 = p2.keyfield1 and
p1.keyfield2 = p2.keyfieldN
where p2.keyfield1 is null