Re: Finding duplicate rows in a table

From: Andre van Winssen <andrevw_at_ideta.nl>
Date: Wed, 13 Jul 1994 11:13:42 GMT
Message-ID: <CsvLuv.14v_at_ideta.nl>

jameson_at_coupler.drink-eid (131W10000-JamesonJ(DR2483)56) writes:

>There are several tables in our database that contain duplicate records. Isn't there
>some simple SQL code to locate duplicate rows in a table? I would really appreciate
>it if you could post a sample.
 

> Thanks,
 

> Jeremy Jameson

If you want to locate them you could use:

select col1,col2,...,lastcol,count(*) -- count tells you how many times

from   your_table                      -- the same row exists in your table

group by col1,col2,...,lastcol -- list all the columns of table your_table having count(*) > 1;

If you want to get rid of duplicate rows use:

delete from your_table A
where A.rowid >

   ( select min(B.rowid)

     from   your_table B
     where  A.col1   = B.col1
     and    A.col2   = B.col2
     and    A.col... = B.col...
     and    A.lastcol= B.lastcol

   );

Hope this'll help

Andre van Winssen

-- 
Andre van Winssen <andrevw_at_ideta.nl>		
Ideta b.v., Frankemaheerd 6, 1102 AN Amsterdam, the Netherlands

Received on Wed Jul 13 1994 - 13:13:42 CEST