> create cursor Schnaps ; > ( c1 c(1), ; > c2 c(1),; > c3 c(1),; > iOrder I ; > ) > > insert into Schnaps values ('A', 'a', '1',1) > insert into Schnaps values ('A', 'b', '2',2) > insert into Schnaps values ('A', 'b', '3',3) > insert into Schnaps values ('A', 'b', '3',4) > insert into Schnaps values ('B', 'c', '4',5) > insert into Schnaps values ('B', 'd', '4',6) >>
>('A', 'a', '1',1) >('A', 'b', '2',2) >('A', 'b', '3',3) >('B', 'c', '4',5) >>
> insert into Schnaps values ('A', 'b', '3',3) > insert into Schnaps values ('A', 'b', '3',4)
create cursor Schnaps ; ( c1 c(1), ; c2 c(1),; c3 c(1),; iOrder I ; ) insert into Schnaps values ('A', 'a', '1',1) insert into Schnaps values ('A', 'b', '2',2) insert into Schnaps values ('A', 'b', '3',3) insert into Schnaps values ('A', 'b', '3',4) && insert into Schnaps values ('B', 'c', '4',5) insert into Schnaps values ('B', 'd', '4',6) && insert into Schnaps values ('B', 'd', '4',7) &&I have added iOrder 7. && are the records that have to be deleted
select S.* ; from Schnaps S, ; ( ; select c3 ; from Schnaps ; group by 1 ; having ( count( *) > 1) ; && You just changed the rules ) AA ; where ( S.c3 == AA.c3 ) ; into cursor duplicates ; order by S.c3, S.iOrderNow add a recno for the self join
&& add rec no select duplicates.*, ; recno() as rec ; from duplicates ; into cursor duplicates_recno; order by c3, iOrderdelete all except first
delete from Schnaps ; where ( ; iOrder in ; ( ; select X1.Iorder ; from duplicates_recno X1 ; left join duplicates_recno X2 on ( X1.c3 == X2.c3 ) ; and ( X1.rec -1 == X2.rec ) ; where ( X2.c3 is not null ) ; ) ; );And voila