Re: Delete duplicates
От | Paul Thomas |
---|---|
Тема | Re: Delete duplicates |
Дата | |
Msg-id | 20030622120335.C4067@bacon обсуждение исходный текст |
Ответ на | Delete duplicates ("Rudi Starcevic" <rudi@oasis.net.au>) |
Список | pgsql-sql |
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 | +------------------------------+---------------------------------------------+
В списке pgsql-sql по дате отправления: