RE: removing duplicate records

From: Jackie Brock <jbrock_at_energyvelocity.com>
Date: Thu, 5 Aug 2004 10:05:28 -0700
Message-ID: <1ECF062C90A28640980C1BB8501D18E00DE08A@mail01.corp.globalenergy.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ivan Chow Sent: Thursday, August 05, 2004 11:01 AM To: oracle-l_at_freelists.org
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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://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_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

---

Received on Thu Aug 05 2004 - 12:02:55 CDT