Deletion of duplicate values

[abbaschoudry]

hi,

i am trying to delete rows where a particular column (hours) has the same value for the same member but where the effective dates are different. i want to delete the duplicate(s) rows which have the most recent effective date(s).

ok, here are 3 rows of data (only including the columns of importance) for one member...

userid: 1093419
catid: 1300
effdate: 2000-08-01 00:00:00.000
value: 37.000000

userid: 1093419
catid: 1300
effdate: 2006-01-13 00:00:00.000
value: 23.000000

userid: 1093419
catid: 1300
effdate: 2006-10-02 00:00:00.000
value: 37.000000

so in the above, i would want to delete all rows that hold duplicate hours (value) with the most recent effdate's for any member who has the same amount of hours (value) for catid 1300 apart from the row with the first instance of that value...if that makes sense?

so the row i would want to delete from the above 3 would be row 3 because rows 1 and 3 both have a value of 37.000000 for catid 1300 for the same member AND the effdate of row 3 is greater than the effdate of row 1...

so far, i've managed to isolate the data that i want to keep (minimum effdate record for any duplicate values with differing effdates for cat id 1300) by using the following sql:

select min(effdate) as effdate from decimalhistory where catid = 1300 group by userid, value having count(value)>1

can someone help me with deleting the rest of the duplicate records please?

 

[rac2]

... managed to isolate the data that i want to keep (minimum effdate record for any duplicate values with differing effdates for cat id 1300 ...

If you have a query that exactly retrieves the rows you wish to keep, then you could create a new table like the old table except empty. Load the good rows using an INSERT query. Rename the tables so the new table has the data you wish to keep.