Received: from trends.net (root@clio.trends.ca [209.47.148.2]) by hub.org (8.9.1/8.9.1) with ESMTP id NAA05760 for ; Thu, 3 Dec 1998 13:52:48 -0500 (EST) (envelope-from sferac@bo.nettuno.it) Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by trends.net (8.8.8/8.8.8) with ESMTP id JAA05603 for ; Thu, 3 Dec 1998 09:47:21 -0500 (EST) 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 PAA12359; Thu, 3 Dec 1998 15:47:03 +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 0zlbm6-00068x-00; Thu, 3 Dec 1998 16:38:42 +0000 Date: Thu, 3 Dec 1998 15:47:01 +0100 From: Sferacarta Software X-Mailer: The Bat! (v1.017) UNREG Reply-To: Sferacarta Software Organization: Sferacarta Software Priority: Normal Message-ID: <2657.981203@bo.nettuno.it> To: Mario Filipe , pgsql-sql@postgresql.org Subject: Re[2]: [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 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 query should retrieve all rows where COLUNA field contains duplicate values. Unfortunately, seems it doesn't work on v6.4, but you may do: select COLUNA from TABELA group by COLUNA having 1 < count(COLUNA) to display only COLUNA field. EXAMPLE: select * from emp; ename |empno|job | hiredate|sal |comm|deptno|level| mgr ------+-----+----------+----------+---------+----+------+-----+---- ALLEN | 7499|SALESMAN |1981-02-20|$1,600.00| 300| 20| 4|7782 BLAKE | 7698|MANAGER |1981-05-01|$2,850.00| | 30| 3|7782 JONES | 7900|CLERK |1981-12-03|$950.00 | | 30| 2|7782 MILLER| 7654|SALESMAN |1981-09-28|$1,250.00| 400| 30| 3|7839 CLARK | 7844|SALESMAN |1981-09-08|$1,500.00| | 10| 2|7839 KING | 7521|SALESMAN |1981-02-22|$1,250.00| 500| 10| 1|7782 ALLEN | 7499|SALESMAN |1981-02-20|$1,600.00| 300| 20| 4|7782 BLAKE | 7698|MANAGER |1981-05-01|$2,850.00| | 30| 3|7782 (8 rows) select ename from emp group by ename having 1 < count(ename); ename ----- ALLEN BLAKE (2 rows) -Jose'-