Received: from trends.net (root@clio.trends.ca [209.47.148.2]) by hub.org (8.9.1/8.9.1) with ESMTP id NAA05836 for ; Thu, 3 Dec 1998 13:52:52 -0500 (EST) (envelope-from mjnf@neptuno.sc.uevora.pt) Received: from neptuno.uevora.pt (neptuno.sc.uevora.pt [193.136.216.24]) by trends.net (8.8.8/8.8.8) with ESMTP id KAA06302 for ; Thu, 3 Dec 1998 10:08:34 -0500 (EST) Received: (from mjnf@localhost) by neptuno.uevora.pt (8.9.1a/8.9.1/Debian/GNU) id PAA08380; Thu, 3 Dec 1998 15:07:53 GMT Message-ID: X-Mailer: XFMail 1.3 [p0] on Linux X-Priority: 3 (Normal) MIME-Version: 1.0 Content-Type: multipart/signed; boundary="_=XFMail.1.3.p0.Linux:981203150750:11500=_"; micalg=pgp-md5; protocol="application/pgp-signature" In-Reply-To: <2657.981203@bo.nettuno.it> Date: Thu, 03 Dec 1998 15:07:50 -0000 (WET) X-Face: "9hSo9W{D!]@5,5u$o~%u; Y'Jd-soHe$zeqc1MFu&mlE; af,Hbz}V,Le1_5)/fK@@P/#_+, `6TSkyHf39(-wxNK9gi$Oin`[S|xWBJ0T8gQ, m[h$1b?1"}SEpxt+:H-m+%h7|Wy)hM-3, cbg3Y8Y> YJIYJu= Reply-To: mjnf@uevora.pt Organization: Universidade de Evora Sender: mjnf@neptuno.sc.uevora.pt From: Mario Filipe To: Sferacarta Software Subject: RE: Re[2]: [SQL] Query to eliminate duplicates Cc: pgsql-sql@postgresql.org This message is in MIME format --_=XFMail.1.3.p0.Linux:981203150750:11500=_ Content-Type: text/plain; charset=iso-8859-1 On 03-Dec-98 Sferacarta Software wrote: > Hello Mario, > > giovedì, 3 dicembre 98, you wrote: > > > MF> On 02-Dec-98 Sferacarta Software wrote: >>> MF> Is there a way to eliminate duplicate records using just SQL? >>> >>> SELECT DISTINCT ... > > MF> I deserved that! > > MF> The problem is that i have a table where there are duplicate > records > MF> and i want to delete them! I tryed creating a unique index but it told me > it > MF> couldn't because it didn't have a function to do something (I think it is > MF> because i had a boolean field in it) > > > select * from TABELA where COLUNA in ( > select COLUNA from TABELA group by COLUNA having 1 < count(COLUNA) > ); > This would work (i believe you) but in my case i'm looking into a table where there are at least 3 columns to look at! It's a table where I keep the information about wich are the disciplines where a student is enrolled: Ex: numero_aluno|codigo_disciplina|ano_lectivo|epoca|semestre ------------+-----------------+-----------+-----+-------- 10335| 1207|1998/1999 | 1|f 10335| 1208|1998/1999 | 1|f 10335| 1209|1998/1999 | 1|f 10335| 1203|1998/1999 | 1|f 10335| 1205|1998/1999 | 1|f 10335| 1212|1998/1999 | 1|f 10335| 1213|1998/1999 | 1|f 10335| 1215|1998/1999 | 1|f In this case there are no duplicates but in this one there are: numero_aluno|codigo_disciplina|ano_lectivo|epoca|semestre ------------+-----------------+-----------+-----+-------- 13427| 35|1998/1999 | 1|t 13427| 904|1998/1999 | 1|f 13427| 122|1998/1999 | 1|t 13427| 907|1998/1999 | 1|f 13427| 481|1998/1999 | 1|f 13427| 286|1998/1999 | 1|t 13427| 368|1998/1999 | 1|t 13427| 35|1998/1999 | 1|t 13427| 904|1998/1999 | 1|f 13427| 122|1998/1999 | 1|t So a duplicate is actually a record that looks exactly like other record on the table... Thanks for your help anyway Mario Filipe mjnf@uevora.pt http://neptuno.sc.uevora.pt/~mjnf --_=XFMail.1.3.p0.Linux:981203150750:11500=_ Content-Type: application/pgp-signature -----BEGIN PGP MESSAGE----- Version: 2.6.3ia iQEVAwUBNmapRJiyznf+xIrhAQGaqgf/cPc1mD8v06R4GX11iwVhYKXo2Klhzuck pULAER4lyceTelEUhxPnNotgo5gmKMJRXRKPoS4vEZH7AoPRgyCmLOXbmu4bebQr 1qzqeEHRjpF8dtd4VGWz3pWF95icfYxli24750H5hyiXHUNUVgIyrrgl0oJbmfZQ ptekGwj3MeTwPUyiHGrFWhVBwVjOj7cLaNS8IHeTCTBw2BlS09cmqtZlaGyYUcvq u0TZPsarX8804Qb6EjXplchyjjp1FgADdO2/qfm6dhX/xo81eIjwyPwRjARop6Da cmuzbIXoYKmuDlO5rwK3KT8tdS7Mz3wudAhA7r3Nnlm/LBIttco6xw== =nS6O -----END PGP MESSAGE----- --_=XFMail.1.3.p0.Linux:981203150750:11500=_-- End of MIME message