RE: removing duplicate records

From: Ivan Chow <ichow2_at_hotmail.com>
Date: Thu, 05 Aug 2004 13:32:58 -0400
Message-ID: <BAY12-F7qljWwY0VnkB0000090b@hotmail.com>

Thanks! That works.

Ivan

Jackie Brock <jbrock_at_energyvelocity.com> wrote: 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

---

---

MSN Toolbar provides one-click access to Hotmail from any Web page – FREE download! http://toolbar.msn.click-url.com/go/onm00200413ave/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

---

Received on Thu Aug 05 2004 - 12:30:24 CDT