Deleting duplicate records. [message #411773] |
Mon, 06 July 2009 12:34 |
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 #411778 is a reply to message #411774] |
Mon, 06 July 2009 12:51 |
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 #411816 is a reply to message #411773] |
Tue, 07 July 2009 00:18 |
|
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), runSQL> 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
|
|
|
|