Help: Delete all rows except 1 of a set of rows.

Using Oracle 7.3.3.2, updating via SQL*Plus.

What I need to do is select from a table all non unique records with certain criteria. If there are rows that match, delete all of the rows except one (it doesn't matter which one it is).

In a nutshell, there should of been a unique key assigned to a table, but there wasn't. I need to clean up the table and create a new unique key when done.

I can find all of the rows that have duplicates using:

SQL> select risn, crit_code, crit_rev from ri_result group by risn, crit_code, crit_rev having count(*) > 1;

I just don't know how to do the delete, leaving 1 row of each.

Thanks for any help,
 Rich W. Received on Wed Sep 16 1998 - 11:58:16 CDT