Home » SQL & PL/SQL » SQL & PL/SQL » Removal of more than two duplicates..
icon14.gif  Removal of more than two duplicates.. [message #210266] Wed, 20 December 2006 01:38 Go to next message
jvnaveen
Messages: 29
Registered: March 2005
Location: Bangalore
Junior Member
Hi Friends,

I have seen many codes in forum for deleting duplicates from a table (that is two entry for each record). But how to delete if we have more than 2 duplicates?. If a particlar column has duplcate values more than 2 times, I would like to retain ONLY one amoung them? How to go abt it?

Regards,
Naveen JV
Re: Removal of more than two duplicates.. [message #210268 is a reply to message #210266] Wed, 20 December 2006 01:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you _try_ the examples?
Re: Removal of more than two duplicates.. [message #210272 is a reply to message #210268] Wed, 20 December 2006 02:07 Go to previous messageGo to next message
jvnaveen
Messages: 29
Registered: March 2005
Location: Bangalore
Junior Member
Hi Frank, May I know what examples?
Re: Removal of more than two duplicates.. [message #210276 is a reply to message #210272] Wed, 20 December 2006 02:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
I have seen many codes in forum for deleting duplicates from a table


Those examples
Re: Removal of more than two duplicates.. [message #210284 is a reply to message #210276] Wed, 20 December 2006 02:59 Go to previous messageGo to next message
jvnaveen
Messages: 29
Registered: March 2005
Location: Bangalore
Junior Member
Hi Frank,

I have tried the code given in the forum. It removes 1 row if we have 2 duplicate rows. If I have 3 or more rows with duplicate values for a particular column, still it removes one row. So, the code is not eliminating the duplicates. What I want is, code should remove all the duplicate rows retaining only one irrespective of number of duplicates.

Thanks
Naveen
Re: Removal of more than two duplicates.. [message #210285 is a reply to message #210284] Wed, 20 December 2006 03:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Which example exactly did you try? I am positive that most examples would do exactly what you ask for.
Re: Removal of more than two duplicates.. [message #210290 is a reply to message #210285] Wed, 20 December 2006 03:15 Go to previous message
jvnaveen
Messages: 29
Registered: March 2005
Location: Bangalore
Junior Member
Ok Frank sorry to distrub you. I got the solution using the following the code:

DELETE FROM my_table
WHERE ROWID NOT IN ( SELECT MIN(ROWID)
FROM my_table
GROUP BY delete_col_name
);

Thanks,
Naveen
Previous Topic: Inserting column
Next Topic: tree view query
Goto Forum:
  


Current Time: Sat Dec 03 10:17:43 CST 2016

Total time taken to generate the page: 0.18100 seconds