Re: Duplicate Records SQL Question

"Falko Rotter" <fantomas99_at_gmx.de> wrote in message news:9lql5r$ll7$1_at_newsread2.nexgo.de...
>
>
> 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
>
>

A more easy solution

select <primary key columns> , count(*)
from table
group by <primary key columns>
having count(*) > 1

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Mon Aug 20 2001 - 14:30:36 CDT