Re[2]: [SQL] Query to eliminate duplicates

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'-