X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 9EEB530EFD1 for ; Sun, 22 Jun 2003 18:33:11 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 60290-07 for ; Sun, 22 Jun 2003 15:33:00 -0300 (ADT) Received: from mta2.entelchile.net (mta14.entelchile.net [164.77.62.24]) by svr1.postgresql.org (Postfix) with ESMTP id 9539130DBA3 for ; Sun, 22 Jun 2003 15:32:59 -0300 (ADT) Received: from german ([164.77.119.99]) by mta2.entelchile.net with ESMTP id <20030622182959.ZBTM12530.mta2@german> for ; Sun, 22 Jun 2003 14:29:59 -0400 From: =?iso-8859-1?Q?Germ=E1n_Guti=E9rrez?= To: Subject: Re: Delete duplicates Date: Sun, 22 Jun 2003 14:33:06 -0400 MIME-Version: 1.0 Message-ID: <000a01c338ec$b9635d00$eaf1fea9@german> X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 Content-Type: multipart/signed; micalg=SHA1; protocol="application/x-pkcs7-signature"; boundary="----=_NextPart_000_0006_01C338CB.2F4E19F0" In-Reply-To: <20030622091522.M37248@oasis.net.au> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Archive-Number: 200306/245 X-Sequence-Number: 14003 This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C338CB.2F4E19F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, you need find duplicates and then you remove them delete from aap where id not in ( select max(id) from aap b where aap.keyword =3D b.keyword ); Germ=E1n Sorry about my english -----Mensaje original----- De: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] En nombre de Rudi Starcevic Enviado el: Domingo, 22 de Junio de 2003 5:15 Para: pgsql-sql@postgresql.org Asunto: [SQL] Delete duplicates 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 =3D a2.keyword ) Regards Rudi. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org ------=_NextPart_000_0006_01C338CB.2F4E19F0 Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIIH5jCCA2IwggLLoAMCAQICEAvaCxfBP4mOqwl0erTOLjMwDQYJKoZI hvcNAQECBQAwXzELMAkGA1UEBhMCVVMxFzAVBgNVBAoTDlZlcmlTaWduLCBJ bmMuMTcwNQYDVQQLEy5DbGFzcyAxIFB1YmxpYyBQcmltYXJ5IENlcnRpZmlj YXRpb24gQXV0aG9yaXR5MB4XDTk4MDUxMjAwMDAwMFoXDTA4MDUxMjIzNTk1 OVowgcwxFzAVBgNVBAoTDlZlcmlTaWduLCBJbmMuMR8wHQYDVQQLExZWZXJp U2lnbiBUcnVzdCBOZXR3b3JrMUYwRAYDVQQLEz13d3cudmVyaXNpZ24uY29t L3JlcG9zaXRvcnkvUlBBIEluY29ycC4gQnkgUmVmLixMSUFCLkxURChjKTk4 MUgwRgYDVQQDEz9WZXJpU2lnbiBDbGFzcyAxIENBIEluZGl2aWR1YWwgU3Vi c2NyaWJlci1QZXJzb25hIE5vdCBWYWxpZGF0ZWQwgZ8wDQYJKoZIhvcNAQEB BQADgY0AMIGJAoGBALtaRIoEFrtV/QN6ii2UTxV4NrgNSrJvnFS/vOh3Kp25 8Gi7ldkxQXB6gUu5SBNWLccI4YRCq8CikqtEXKpC8IIOAukv+8I7u77JJwpd trA2QjO1blSIT4dKvxna+RXoD4e2HOPMxpqOf2okkuP84GW6p7F+78nbN2rI SsgJBuSZAgMBAAGjgbAwga0wDwYDVR0TBAgwBgEB/wIBADBHBgNVHSAEQDA+ MDwGC2CGSAGG+EUBBwEBMC0wKwYIKwYBBQUHAgEWH3d3dy52ZXJpc2lnbi5j b20vcmVwb3NpdG9yeS9SUEEwMQYDVR0fBCowKDAmoCSgIoYgaHR0cDovL2Ny bC52ZXJpc2lnbi5jb20vcGNhMS5jcmwwCwYDVR0PBAQDAgEGMBEGCWCGSAGG +EIBAQQEAwIBBjANBgkqhkiG9w0BAQIFAAOBgQACfZ5vRUs4oLje6VNkIbzk TCuPHv6SQKzYCjlqoTIhLAebq1n+0mIafVU4sDdz3PQHZmNiveFTcFKH56jY UulbLarh3s+sMVTUixnI2COo7wQrMn0sGBzIfImoLnfyRNFlCk10te7TG5Jz dC6JOzUTcudAMZrTssSr51a+i+P7FTCCBHwwggPloAMCAQICEA2zPe1bokNX dAUBAzBV3LswDQYJKoZIhvcNAQEEBQAwgcwxFzAVBgNVBAoTDlZlcmlTaWdu LCBJbmMuMR8wHQYDVQQLExZWZXJpU2lnbiBUcnVzdCBOZXR3b3JrMUYwRAYD VQQLEz13d3cudmVyaXNpZ24uY29tL3JlcG9zaXRvcnkvUlBBIEluY29ycC4g QnkgUmVmLixMSUFCLkxURChjKTk4MUgwRgYDVQQDEz9WZXJpU2lnbiBDbGFz cyAxIENBIEluZGl2aWR1YWwgU3Vic2NyaWJlci1QZXJzb25hIE5vdCBWYWxp ZGF0ZWQwHhcNMDMwNjIwMDAwMDAwWhcNMDMwODE5MjM1OTU5WjCCASAxFzAV BgNVBAoTDlZlcmlTaWduLCBJbmMuMR8wHQYDVQQLExZWZXJpU2lnbiBUcnVz dCBOZXR3b3JrMUYwRAYDVQQLEz13d3cudmVyaXNpZ24uY29tL3JlcG9zaXRv cnkvUlBBIEluY29ycC4gYnkgUmVmLixMSUFCLkxURChjKTk4MR4wHAYDVQQL ExVQZXJzb25hIE5vdCBWYWxpZGF0ZWQxJzAlBgNVBAsTHkRpZ2l0YWwgSUQg Q2xhc3MgMSAtIE1pY3Jvc29mdDEjMCEGA1UEAxQaR2VybeFuIFIuIEd1dGnp cnJleiBHYXlvc28xLjAsBgkqhkiG9w0BCQEWH2dlcm1hbmd1dGllcnJlemdA ZW50ZWxjaGlsZS5uZXQwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAL4I XnAvpmxSykKaQMFiTUquBb0Ni+28GKdDr0XFjqCjp+9FEkm9IV5QS/RtOAqO 8teGJVjmdmE6bStChH1XMgvJz/i7W6iP3e5gF4Yp230u3GRYqGon0A6wGYPa f2UzruRgVBNX70faftkxbfzEfS4lxHlYtKc43AQ1DALVoFYlAgMBAAGjggEG MIIBAjAJBgNVHRMEAjAAMIGsBgNVHSAEgaQwgaEwgZ4GC2CGSAGG+EUBBwEB MIGOMCgGCCsGAQUFBwIBFhxodHRwczovL3d3dy52ZXJpc2lnbi5jb20vQ1BT MGIGCCsGAQUFBwICMFYwFRYOVmVyaVNpZ24sIEluYy4wAwIBARo9VmVyaVNp Z24ncyBDUFMgaW5jb3JwLiBieSByZWZlcmVuY2UgbGlhYi4gbHRkLiAoYyk5 NyBWZXJpU2lnbjARBglghkgBhvhCAQEEBAMCB4AwMwYDVR0fBCwwKjAooCag JIYiaHR0cDovL2NybC52ZXJpc2lnbi5jb20vY2xhc3MxLmNybDANBgkqhkiG 9w0BAQQFAAOBgQB4PVxhwIAT9yFhCm1JZKHFb3y+PCq8FkalUwUEa0zgGHpt b+rwi4isq8VCCJJXOUYzregEPf0RRP3qAdb4rk79XhghTslMZod2Vl/MRe2a EAIXYHe3SZ+qyDrvNkrvK7z37yS/qA3e59KQQMYioZB4XzyEfUDigCuj8htF Z+qQpzGCBD4wggQ6AgEBMIHhMIHMMRcwFQYDVQQKEw5WZXJpU2lnbiwgSW5j LjEfMB0GA1UECxMWVmVyaVNpZ24gVHJ1c3QgTmV0d29yazFGMEQGA1UECxM9 d3d3LnZlcmlzaWduLmNvbS9yZXBvc2l0b3J5L1JQQSBJbmNvcnAuIEJ5IFJl Zi4sTElBQi5MVEQoYyk5ODFIMEYGA1UEAxM/VmVyaVNpZ24gQ2xhc3MgMSBD QSBJbmRpdmlkdWFsIFN1YnNjcmliZXItUGVyc29uYSBOb3QgVmFsaWRhdGVk AhANsz3tW6JDV3QFAQMwVdy7MAkGBSsOAwIaBQCgggKyMBgGCSqGSIb3DQEJ AzELBgkqhkiG9w0BBwEwHAYJKoZIhvcNAQkFMQ8XDTAzMDYyMjE4MzMwMVow IwYJKoZIhvcNAQkEMRYEFP9j5H/yQbHIH3U/fy2OFXRqZe11MGcGCSqGSIb3 DQEJDzFaMFgwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0GCCqGSIb3 DQMCAgFAMAcGBSsOAwIHMA0GCCqGSIb3DQMCAgEoMAcGBSsOAwIaMAoGCCqG SIb3DQIFMIHyBgkrBgEEAYI3EAQxgeQwgeEwgcwxFzAVBgNVBAoTDlZlcmlT aWduLCBJbmMuMR8wHQYDVQQLExZWZXJpU2lnbiBUcnVzdCBOZXR3b3JrMUYw RAYDVQQLEz13d3cudmVyaXNpZ24uY29tL3JlcG9zaXRvcnkvUlBBIEluY29y cC4gQnkgUmVmLixMSUFCLkxURChjKTk4MUgwRgYDVQQDEz9WZXJpU2lnbiBD bGFzcyAxIENBIEluZGl2aWR1YWwgU3Vic2NyaWJlci1QZXJzb25hIE5vdCBW YWxpZGF0ZWQCEA2zPe1bokNXdAUBAzBV3LswgfQGCyqGSIb3DQEJEAILMYHk oIHhMIHMMRcwFQYDVQQKEw5WZXJpU2lnbiwgSW5jLjEfMB0GA1UECxMWVmVy aVNpZ24gVHJ1c3QgTmV0d29yazFGMEQGA1UECxM9d3d3LnZlcmlzaWduLmNv bS9yZXBvc2l0b3J5L1JQQSBJbmNvcnAuIEJ5IFJlZi4sTElBQi5MVEQoYyk5 ODFIMEYGA1UEAxM/VmVyaVNpZ24gQ2xhc3MgMSBDQSBJbmRpdmlkdWFsIFN1 YnNjcmliZXItUGVyc29uYSBOb3QgVmFsaWRhdGVkAhANsz3tW6JDV3QFAQMw Vdy7MA0GCSqGSIb3DQEBAQUABIGAmQ0vsVFEoUAPttE04xWWaOjEa9hWLfDC wwn6Yzngasnps90Y4lgToM7gEmQ4tETdc7nRnaOjGGhUz5Tyy+xpROEoltAs eLlgpg0zoSy49Cyv05naQprZtjQAyP7qwXKHREaBTc0Al3lzFr4cYcyQxkNq FvG4K4S+cisjielAJZIAAAAAAAA= ------=_NextPart_000_0006_01C338CB.2F4E19F0--