How to fix data after changing key

Question

I have a table with several integers as primary key. One of them is a counter. I need to remove the counter from the key.

Removing counter from the key will make many rows duplicates (with different counter values but all other key elements the same).

I need a query that will delete all duplicates and leave only the row with the highest counter value. Any help is appreciated.

Answer 1

How about this:

create table foo (
  a number,
  b number,
  c number,
  constraint pk_foo primary key (a, b, c)
);
insert into foo (a, b, c) values (0, 0, 0);
insert into foo (a, b, c) values (0, 0, 1);
insert into foo (a, b, c) values (0, 1, 0);
insert into foo (a, b, c) values (0, 1, 1);
insert into foo (a, b, c) values (1, 0, 0);
insert into foo (a, b, c) values (1, 0, 1);
insert into foo (a, b, c) values (1, 1, 0);
insert into foo (a, b, c) values (1, 1, 1);
delete from foo t1
 where t1.c not in (
         select max(t2.c)
           from foo t2
          group by a, b
       )
;
select * from foo;

PS: You'll have to delete before removing c from the primary key.