deleting duplicate records [message #39637] |
Tue, 30 July 2002 07:00 |
Morch
Messages: 1 Registered: July 2002
|
Junior Member |
|
|
I need to delete dumplicate records within a table,
the duplicate records are only duplicated by two key fileds.
Table eg
Conversion_temp_tbl
Fnd=Key1
ComCode=k2
Rn| Fnd| ComCode|Delta| inv_Amt
001| pros | l200 | D | 0.56 | --duplicated
002| pros | f100 | D | 0.85 | --duplicated
003| endw |l200 | D | 0.96 | --duplicated
004| pros | l200 | D | 4.52 | --duplicated
005| pros |l200 | A | 2.87 | --Needs to remain
006| pros |f100 | A | 2.54 | --Needs to remain
007| endw | l200 | A | 0.21 | --Needs to remain
008| pros | l200 | A | 8.65 | --Needs to remain
009| fofu | s300 | D | 0.23 | --The only field not duplicated,
I cannot specifically search for Fnd, ComCode values as we are dealling with thousands of unique records but I need to be able to delete according to (Fnd and ComCode where deltatype = D), I have tried numerous angle's, select distinct's, select unique, self joins, rownum variations etc etc.
A fresh approach would be appreciated, I am probably missing the obvious here.
|
|
|
|