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.
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 |