T-SQL Query that looks for multiple records.

Question

I 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.

Thanks,

-Ben

Answer 1

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.

Select *
From paychecks p
 (
  Select account,
   check_no,
   occurs = count(*)
  From paychecks
  Group
  By account,
   check_no
  Having count(*) > 1
 ) dups
Where p.account = dups.account
And p.check_no = dups.check_no
Order
by p.account,
 p.check_no

Answer 2

select 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