Re: [SQL] Delete duplicates

[Paul Thomas]

On 22/06/2003 10:15 Rudi Starcevic wrote:


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
)
I just tries this with 7.3.3:

select max(id), keyword from aap where keyword in (select distinct keyword 
from aap) group by keyword;

 max |     keyword
-----------------------
   6 | LEAGUE BRONCOS
   4 | LEAGUE PANTHERS
(2 rows)
HTH

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller 
Business |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings