Delete/merge some records based on a count
Posted on 2008-06-16
I am trying to delete/merge some records from my dataset as there are a nuber of duplicates but I'm struggling to figure out how to do this. I have the following code:
SELECT fnam03, snam03, had103, hpc103, hpc203, dtob03, COUNT(*) AS No_of_records
GROUP BY fnam03, snam03, had103, hpc103, hpc203, dtob03
HAVING (COUNT(*) > 1) and snam03 is not null
order by fnam03, snam03
and this allows me to detect the duplicates in my dataset (about 849 from about 12000). However I also need to be able to perform some other calculations prior to the merging taking place:
If there are 2 records (as most of the duplicates are) then I need to check a field called RGNO03 to see if either record has this populated. If one of them does then I wish to keep this record and discard the other. If RGNO03 is NULL in both records then it should check RCNO03 and the record with the highest number in this field should be retained and the lower record discarded. However, if RGNO03 is popluated in both records then no de-duplication should take place.
BTW RGNO03 and RCNO03 are both in the PTP03 table (the same table as the rest of the query fields)
The same principals should be applied to records that have a count value of 3 or 4 (there aren't that many of them so I could do this in stages if needed).
Anyone got any suggestions on a possible way forward on this one?