Re: removing duplicate records
- From: Carel-Jan Engel <cjpengel.dbalert@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 05 Aug 2004 20:57:28 +0200
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@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:
