Delete duplicates

From: "Rudi Starcevic" <rudi(at)oasis(dot)net(dot)au>
To: pgsql-sql(at)postgresql(dot)org
Subject: Delete duplicates
Date: 2003-06-22 09:15:22
Message-ID: 20030622091522.M37248@oasis.net.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Ian Barwick 2003-06-22 10:20:15 Re: Delete duplicates
Previous Message Tom Lane 2003-06-21 22:18:01 Re: plpgsql, double quoted column names containing spaces/hyphens