From: "Rudi Starcevic" <rudi@oasis.net.au>
To: pgsql-sql@postgresql.org
Subject: Delete duplicates
Date: Sun, 22 Jun 2003 19:15:22 +1000
Message-Id: <20030622091522.M37248@oasis.net.au>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1


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.