Try something like: DELETE emp e1 WHERE EXISTS (SELECT 1 FROM emp e2=20 WHERE e2.id =3D e1.id AND e2.rowid > e1.rowid)=20 HTH -J -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ivan Chow Sent: Thursday, August 05, 2004 11:01 AM To: oracle-l@xxxxxxxxxxxxx Subject: removing duplicate records Hi, I'm not sure how I can achieve this in one SQL statement. I want to remove=20 duplicate id's from the following table. SQL> select * from emp; ID FIRST LAST ---------- ---------- ---------- 1 MIKE SMITH 1 SUE GAINER 1 DEB SHELLY 2 JOHN BAKER 3 DAN DOE 3 MIKE DOE 6 rows selected. The results after removing the duplicates: ID FIRST LAST ---------- ---------- ---------- 1 MIKE SMITH 2 JOHN BAKER 3 DAN DOE For each duplicate id, it does not matter which id I retain. It can be MIKE=20 SMITH or SUE GAINER or DEB SHELLY. thanks. Ivan _________________________________________________________________ FREE pop-up blocking with the new MSN Toolbar - get it now!=20 http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/ ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------