Thread: How to delete completely duplicate rows

Hi,
 
I want to delete duplicates in my table. I've dropped the unique constraint to insert my data.
My id value is a hash calculated witch the values of the two other columns.
So I want to delete all columns, which are indentical, but keeping one.
 
DELETE FROM table t1 USING table t2 WHERE t1.id = t2.id AND t1.ctid > t2.ctid

But the oids aren't unique enough.
What else could I do?

Janek

With C as (
Select row_number() over partition by (list, all, columns, here order by oid) as rn
)
Delete
From C
Where rn > 1;

Thanks!
It's working
 
Janek

On Thu, Jan 2, 2014 at 4:33 AM, Janek Sendrowski <janek12@web.de> wrote:
> Thanks!
> It's working

you can do it without listing columns via:

select *, row_number() over(partition by c) from c;

emphasis on 'partition by c'.

merlin