Re: removing duplicate records

From: Carel-Jan Engel <cjpengel.dbalert_at_xs4all.nl>
Date: Thu, 05 Aug 2004 20:57:28 +0200
Message-Id: <1091732248.6060.140.camel@dbalert199.dbalert.nl>

Tom had an elegant solution for this in last OraMag using analytics. Translated to your situation:

delete from emp
where rowid in
(select rid

        from
        (select rowid rid,
                     rownumber() over
                        (partition by
                              id
                         order by rowid ) rn

from t
)
where rn <> 1
)
/

Best regards,

Carel-Jan Engel

===
If you think education is expensive, try ignorance. (Derek Bok) ===
On Thu, 2004-08-05 at 19:01, Ivan Chow wrote:

> Hi,
> I'm not sure how I can achieve this in one SQL statement. I want to remove
> 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
> SMITH or SUE GAINER or DEB SHELLY.
>
> thanks.
>
> Ivan
>
> _________________________________________________________________
> FREE pop-up blocking with the new MSN Toolbar get it now!
> 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 - 13:50:02 CDT