Received: from smtp0.mindspring.com (smtp0.mindspring.com [207.69.200.30]) by hub.org (8.9.2/8.9.1) with ESMTP id PAA94362 for ; Tue, 30 Mar 1999 15:36:57 -0500 (EST) (envelope-from clark.evans@manhattanproject.com) Received: from manhattanproject.com (pool-209-138-0-123.nwrk.grid.net [209.138.0.123]) by smtp0.mindspring.com (8.8.5/8.8.5) with ESMTP id PAA15522 for ; Tue, 30 Mar 1999 15:36:55 -0500 (EST) Sender: clark@mindspring.com Message-ID: <37013500.DFF0A64A@manhattanproject.com> Date: Tue, 30 Mar 1999 20:33:04 +0000 From: Clark Evans X-Mailer: Mozilla 4.51 [en] (X11; I; Linux 2.0.36 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@hub.org Subject: Selecting and deleting duplicate rows References: <37010D69.6D1868ED@compclass.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit This is a question I've seen a few times, and had to research, so I figured I'd share the answer. ------------------------------------------------- drop table test; -- create table test ( a text, b text ); -- unique values insert into test values ( 'x', 'y'); insert into test values ( 'x', 'x'); insert into test values ( 'y', 'y' ); insert into test values ( 'y', 'x' ); -- duplicate values insert into test values ( 'x', 'y'); insert into test values ( 'x', 'x'); insert into test values ( 'y', 'y' ); insert into test values ( 'y', 'x' ); -- one more double duplicate insert into test values ( 'x', 'y'); -- select oid, a, b from test; -- -- select duplicate rows -- select o.oid, o.a, o.b from test o where exists ( select 'x' from test i where i.a = o.a and i.b = o.b and i.oid < o.oid ); -- -- delete duplicate rows -- -- Note: PostgreSQL dosn't support aliases on -- the table mentioned in the from clause -- of a delete. -- delete from test where exists ( select 'x' from test i where i.a = test.a and i.b = test.b and i.oid < test.oid ); -- -- Let's see if it worked. -- select oid, a, b from test; -- -- Delete duplicates with respect to a only, ignoring -- the value in b. Note, the first deletion leaves the -- first oid with the unique values and removes subsequent -- ones, in this delete we reverse the direction of the < -- to save the last oid, and remove the previous ones. -- delete from test where exists ( select 'x' from test i where i.a = test.a and i.oid > test.oid ); -- -- Let's see if it worked. -- select oid, a, b from test;