Re: removing duplicates - sql

On Thursday 22 August 2002 08:13 am, Tommi Maekitalo wrote:
> Am Donnerstag, 22. August 2002 01:52 schrieb Johnson, Shaunn:
> > Howdy:
> >
> > I have a table that has a primary key on it.  Somewhere in
> > the table, it seems to have a few duplicate records in it.
> > I'm trying to weed out the duplicates, but am having problems
> > with the sql.
>
> ...
>
> Hi,
>
> select distinct t1.name, t1.addr1, t1.phone, t1.city
>   from emp_table t1
>  where (select count(*) from emp_table t2
>          where t1.name = t2.name
>            and t1.addr1 = t2.addr1
>            and t1.phone = t2.phone
>            and t1.city = t2.city) > 1
>
> will do it.
 or

  select name,addr,phone,city,count(*)
  from emo_table
  group by name,addr,phone,city
  having count(*) > 1

regards
peco