Re: Duplicate Records SQL Question

Hi,

At first: i dont like it to answer anonymous persons. Please show your real name in the usenet. thx :-)

> I am trying to pull duplicate records out of a database that has the
> following fields:
>
> Month, Memcode, Memid, PayAmt
>
> How do I list only the duplicate records for every month that have a
> common memcode and payamt?
>

select t1.memid, t1.month, t1.memcode, t1.payment from table1 t1,
(select month, memcode, payment
from table1
group by month, memcode, payment
having count>1) vw1

where t1.month=vw1.month
  and t1.memcode=vw1.memcode
  and t1.payment=vw1.payment

this query gives back the memid, month, memcode and payments of all duplicated records - But dont delete all this records, because one record of every group is the correct dataset. If you delete the records of the groups (up to the last one in very group) you must according to adapt the statement.

Regards,
Falko Rotter

• Software Developer -

Rotter & Kalweit Softwaredesign GbR
Friemarer Straße 38

99867 Gotha
GERMANY mail: news_at_falko-rotter.de Received on Mon Aug 20 2001 - 04:28:30 CDT