Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.1/8.9.1) with ESMTP id KAA21244 for ; Fri, 4 Dec 1998 10:16:47 -0500 (EST) (envelope-from sferac@bo.nettuno.it) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.8.6/8.8.6/NETTuno 3.1) with ESMTP id QAA06098; Fri, 4 Dec 1998 16:16:22 +0100 (MET) Received: from blu.sferacarta.com (mail.sferacarta.com) [10.20.30.12] by proxy.sferacarta.com with smtp (Exim 2.05 #1 (Debian)) id 0zlyGy-0004IV-00; Fri, 4 Dec 1998 16:40:04 +0000 Date: Fri, 4 Dec 1998 15:48:29 +0100 From: Sferacarta Software X-Mailer: The Bat! (v1.017) UNREG Reply-To: Sferacarta Software Organization: Sferacarta Software Priority: Normal Message-ID: <5658.981204@bo.nettuno.it> To: Mario Filipe , pgsql-sql@postgresql.org Subject: Re[4]: [SQL] Query to eliminate duplicates References: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit Hello Mario, giovedì, 3 dicembre 98, you wrote: MF> 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) >> ); >> MF> This would work (i believe you) but in my case i'm looking into a table MF> where there are at least 3 columns to look at! MF> It's a table where I keep the information about wich are the MF> disciplines where a student is enrolled: MF> Ex: MF> numero_aluno|codigo_disciplina|ano_lectivo|epoca|semestre MF> ------------+-----------------+-----------+-----+-------- MF> 10335| 1207|1998/1999 | 1|f MF> 10335| 1208|1998/1999 | 1|f MF> 10335| 1209|1998/1999 | 1|f MF> 10335| 1203|1998/1999 | 1|f MF> 10335| 1205|1998/1999 | 1|f MF> 10335| 1212|1998/1999 | 1|f MF> 10335| 1213|1998/1999 | 1|f MF> 10335| 1215|1998/1999 | 1|f MF> In this case there are no duplicates but in this one there are: MF> numero_aluno|codigo_disciplina|ano_lectivo|epoca|semestre MF> ------------+-----------------+-----------+-----+-------- MF> 13427| 35|1998/1999 | 1|t MF> 13427| 904|1998/1999 | 1|f MF> 13427| 122|1998/1999 | 1|t MF> 13427| 907|1998/1999 | 1|f MF> 13427| 481|1998/1999 | 1|f MF> 13427| 286|1998/1999 | 1|t MF> 13427| 368|1998/1999 | 1|t MF> 13427| 35|1998/1999 | 1|t MF> 13427| 904|1998/1999 | 1|f MF> 13427| 122|1998/1999 | 1|t MF> So a duplicate is actually a record that looks exactly like other record on the MF> table... MF> Thanks for your help anyway MF> Mario Filipe MF> mjnf@uevora.pt MF> http://neptuno.sc.uevora.pt/~mjnf select * from cursos; 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 (10 rows) create table temp as select distinct * from cursos; SELECT select * from temp; numero_aluno|codigo_disciplina|ano_lectivo|epoca|semestre ------------+-----------------+-----------+-----+-------- 13427| 35|1998/1999 | 1|t 13427| 122|1998/1999 | 1|t 13427| 286|1998/1999 | 1|t 13427| 368|1998/1999 | 1|t 13427| 481|1998/1999 | 1|f 13427| 904|1998/1999 | 1|f 13427| 907|1998/1999 | 1|f (7 rows) DROP TABLE cursos; DROP alter table temp rename to cursos; RENAME -Jose'-