RE: removing duplicate records
- From: "Jackie Brock" <jbrock@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 5 Aug 2004 10:05:28 -0700
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
-----------------------------------------------------------------
Other related posts:
