remove rows with some duplicate column value

Question

Suppose I have a table with column A like following :

a  
--
x  
y  
m  
x  
n  
y  

I want to delete all rows that have duplicate a column value and keep just one value. After this operation, my column would be like If you do :

select distinct a from A;

I know how to select rows with repeated a column values But I can't just replace select with DELETE because it would delete the unique values too.
Any help would be greatly appreciated.

Answer 1

In Oracle, you can do this by using the hidden column rowid and a correlated subquery:

delete from a
    where rowid > (select min(rowid)
                   from a a2
                   where a.a = a2.a
                  );

Alternatively, you can phrase this as a not in:

delete from a
    where rowid not in (select min(rowid)
                        from a a2
                        group by a2.a
                       );