Add primary key to table based on timestamp

Let us say we have the following table structure and values:

FooTable:
    foo1    foo2   timestamp
     1       1      1
     2       2      1
     2       1      2

Currently there is no primary key constraint on the table.

Question:

In Oracle, what is the best way to make foo1 the primary key of the table? Assume that:

1. No two rows will have both the same timestamp and foo1 value
2. The row with the latest timestamp takes precedence (other rows with the same foo1 value should be deleted).

Thus, here is the desired table structure after the query:

FooTable:
    foo1(pk)  foo2   timestamp
     1         1      1
     2         1      2

Note: The main problem is deleting the old duplicate rows. Once this is do开发者_如何学Gone, the following query can be used to setup the primary key without fear of duplicates:

alter table FooTable modify foo1 primary key;

Keep in mind that you're destroying data here. Make sure that you have a good back-up of the database.

I don't work with Oracle, but I think that this is generic enough. I don't know if Oracle allows table aliases in the DELETE clause or not, so you may need to adjust for that:

DELETE FT1
FROM FooTable FT1
WHERE EXISTS (
    SELECT *
    FROM FooTable FT2
    WHERE
        FT2.foo1 = FT1.foo1 AND
        FT2.timestamp > FT1.timestamp
)

If the combination of foo1 and timestamp is unique, you could make a composite key and then make a view where it only showed the maximum timestamp.

This would let you preserve your data.

DELETE FROM foo_table WHERE EXISTS 
  (    
       WITH q0 AS (SELECT foo1 AS f, timestamp AS ts, row_number() AS rn 
         FROM foo_table OVER (PARTITION BY foo1 ORDER by timestamp DESC) )
       SELECT 1 FROM q0 WHERE q0.f=foo1 AND q0.timestamp=ts AND rn>1
  );

Some other versions of SQL allow a JOIN style for the correlated subquery.