Home » SQL & PL/SQL » SQL & PL/SQL » Deleting duplicate records.
Deleting duplicate records. [message #411773] Mon, 06 July 2009 12:34 Go to next message
cs02rm0
Messages: 3
Registered: July 2009
Junior Member
I'm trying to delete some duplicate records, leaving the original records with the highest col_a (number) value.

This query shows the id and values of columns A and B and also a rank value which will be the same value for all cases of a given value for col_a and col_b. Hopefully I've explained that clearly enough!

select col_uid, col_a, col_b,
rank() over(order by col_a, col_b) as rank
from table_a

So essentially what I'm trying to do is end up in a situation where rows are deleted where a rank value is repeated and it should be the row with the highest col_uid value that is left.

Any ideas how I can do it? Presumably there's some way I can query the max(col_uid) for some group by and put the results into a temporary table, delete what was in the original table and then put the contents of the temporary table back in?

I'm using 10g I believe.

Thanks
Re: Deleting duplicate records. [message #411774 is a reply to message #411773] Mon, 06 July 2009 12:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I'm trying to delete some duplicate records

This is a FAQ & solution can be found by SEARCH this forum

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Deleting duplicate records. [message #411778 is a reply to message #411774] Mon, 06 July 2009 12:51 Go to previous messageGo to next message
cs02rm0
Messages: 3
Registered: July 2009
Junior Member
BlackSwan wrote on Mon, 06 July 2009 19:37
This is a FAQ & solution can be found by SEARCH this forum


Maybe you could, I couldn't, so I started a post here for people to suggest helpful solutions not patronise me.

BlackSwan wrote on Mon, 06 July 2009 19:37
You need to help us by following the Posting Guidelines


Any particular ones I missed? I can't see the problem myself and clearly there was enough legible information for you to decide this was a FAQ. Either I need to help you understand the problem or you don't know it's an FAQ, which way would you like to have it? You seem like you're just being awkward to be honest.
Re: Deleting duplicate records. [message #411783 is a reply to message #411778] Mon, 06 July 2009 13:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
"Deleting duplicate records" is a FAQ anyway you say it.

Quote:
Any particular ones I missed?

* oracle version
* formatting
* test case
* searching before posting
...
Do you want us to continue?

Regards
Michel

[Updated on: Mon, 06 July 2009 13:16]

Report message to a moderator

Re: Deleting duplicate records. [message #411785 is a reply to message #411773] Mon, 06 July 2009 13:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>leaving the original records with the highest col_a (number) value.
>it should be the row with the highest col_uid value that is left.
Ready, Fire, Aim!
Please decide what requirement needs to be met.


>put the results into a temporary table, delete what was in the original table and then put the contents of the temporary table back in?
This is not SQLServer
Rarely if ever does Oracle need temporary table.
Re: Deleting duplicate records. [message #411816 is a reply to message #411773] Tue, 07 July 2009 00:18 Go to previous messageGo to next message
Littlefoot
Messages: 21822
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is OraFAQ FAQ page; once you have some spare time, browse it - you'll find some really interesting solutions of your previous, current and future problems.

"Deleting duplicates" is also described in there, in the "SQL FAQ" section. Take a look, try to implement one (or all, just to see how they work) solution and, if you still have problems, come back, provide what you did (copy/paste of your SQL*Plus session would be fine) and someone will certainly help.

cs02rm0
I'm using 10g I believe.
There's an easy way to find that out; while in SQL*Plus (or any other client capable of executing your queries), run
SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Re: Deleting duplicate records. [message #411865 is a reply to message #411773] Tue, 07 July 2009 03:51 Go to previous message
cs02rm0
Messages: 3
Registered: July 2009
Junior Member
Many thanks Littlefoot, a very helpful post, method 3 was the solution I was looking for.
Previous Topic: Date (merged 2) 10g
Next Topic: Materialized View
Goto Forum:
  


Current Time: Wed Nov 13 00:33:36 CST 2024