Re[4]: [SQL] Query to eliminate duplicates - Mailing list pgsql-sql
| From | Sferacarta Software |
|---|---|
| Subject | Re[4]: [SQL] Query to eliminate duplicates |
| Date | |
| Msg-id | 5658.981204@bo.nettuno.it Whole thread Raw |
| In response to | RE: Re[2]: [SQL] Query to eliminate duplicates (Mario Filipe <mjnf@uevora.pt>) |
| Responses |
RE: Re[4]: [SQL] Query to eliminate duplicates
|
| List | pgsql-sql |
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'-