Home » SQL & PL/SQL » SQL & PL/SQL » Delete duplicate rows but keep original (10.2.0.3)
Delete duplicate rows but keep original [message #338836] Wed, 06 August 2008 01:38 Go to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
Hi,
I was trying to delete duplicate rows in a table while keeping one copy for each case. I was able to select the rows using rank(), row_number() functions but could not figure out how I can delete the rows.

here is what i have tried

select * from t1;

      RN
----------
        20
        18
        16
        16
        16
        13
        12
        10
         7
         5
         4


select rn,rnk from (select rn, row_number()over(partition by rn order by rn desc)rnk  from t1)
where rnk >1
        RN        RNK
---------- ----------
        16          2
        16          3



now i wish to delete the above 2 rows from T1. I tried various delete statements but somehow its not clicking as to what should be the where condition in my delete command???

delete from t1  where rnk in  
(
 select rnk from (select rn, row_number()over(partition by rn order by rn desc)rnk  from t1)
where rnk >1
)
Re: Delete duplicate rows but keep original [message #338837 is a reply to message #338836] Wed, 06 August 2008 01:43 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How does one eliminate duplicates rows from a table?
Re: Delete duplicate rows but keep original [message #338864 is a reply to message #338837] Wed, 06 August 2008 02:33 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
Got it.. thanks Smile
Re: Delete duplicate rows but keep original [message #338951 is a reply to message #338864] Wed, 06 August 2008 05:28 Go to previous messageGo to next message
rajivgupta780184
Messages: 3
Registered: August 2008
Location: JAMSHEDPUR
Junior Member
TRY THIS ONE..... Razz
IT WILL WORK....


SQL> DELETE FROM table_name A WHERE ROWID > (
2 SELECT min(rowid) FROM table_name B
3 WHERE A.key_values = B.key_values);
Re: Delete duplicate rows but keep original [message #338957 is a reply to message #338951] Wed, 06 August 2008 05:42 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
rajivgupta780184 this query is already provided via
littlefoot and the problem is already solved.

Please read previous post before posting.

Regards,
Rajat
Re: Delete duplicate rows but keep original [message #401182] Sat, 02 May 2009 01:09 Go to previous messageGo to next message
rajivgupta780184
Messages: 3
Registered: August 2008
Location: JAMSHEDPUR
Junior Member
hi this is rajivgupta@yogiktechnologies.com,this is your solution

delete from emp e1
where e1.rowid >
( select min(e2.rowID) from emp e2
where e1.empname = e2.empname
)


Re: Delete duplicate rows but keep original [message #401184 is a reply to message #338836] Sat, 02 May 2009 01:31 Go to previous messageGo to next message
deepakkrchaturvedy
Messages: 1
Registered: April 2009
Location: jamshedpur
Junior Member

i think this might work

delete from t1 a1
where t.rowid >
( select min(a2.rowID) from emp a2
where a1.empname = a2.empname
)
Re: Delete duplicate rows but keep original [message #401186 is a reply to message #401182] Sat, 02 May 2009 01:36 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@rajivgupta780184

What is your purpose repeating what you posted 9 months ago? Just to put your email online? Good idea to be spammed in return.

Regards
Michel

Previous Topic: New at Creating Functions
Next Topic: SQL,PLSQL mutiple order by with decode issue
Goto Forum:
  


Current Time: Sat Dec 10 08:55:04 CST 2016

Total time taken to generate the page: 0.05749 seconds