Re: Delete duplicates

Hi,

you need find duplicates and then you remove them

delete from aap where id not in ( select max(id) from aap b where
aap.keyword = b.keyword );

Germán
Sorry about my english

-----Mensaje original-----
De: pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] En nombre de Rudi Starcevic
Enviado el: Domingo, 22 de Junio de 2003 5:15
Para: pgsql-sql(at)postgresql(dot)org
Asunto: [SQL] Delete duplicates

Hi,

I have a table with duplicates and trouble with my SQL.
I'd like to keep a single record and remove older duplicates.
For example below of the 6 recods I'd like to keep records
4 and 6.

TABLE: aap
id | keyword
----+-----------------
1 | LEAGUE PANTHERS
2 | LEAGUE PANTHERS
3 | LEAGUE PANTHERS
4 | LEAGUE PANTHERS
5 | LEAGUE BRONCOS
6 | LEAGUE BRONCOS

Here is my SQL so far, it will select records 1 to 5 instead
of 1,2,3 and 5 only.

Any help greatly appreciated. I think I need a Group By somewhere in
there.

select a1.id
from aap a1
where id < ( SELECT max(id) FROM aap AS a2 )
AND EXISTS
(
SELECT *
FROM aap AS a2
WHERE a1.keyword = a2.keyword
)

Regards
Rudi.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org