pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Sferacarta Software <sferac@bo.nettuno.it>
To: Mario Filipe <mjnf@uevora.pt>
To: pgsql-sql@postgresql.org
Subject: Re[2]: [SQL] Query to eliminate duplicates
Date: Thu, 3 Dec 1998 15:47:01 +0100
Message-ID: <2657.981203@bo.nettuno.it> (raw)
References: <XFMail.981203085921.mjnf@uevora.pt>

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





view thread (3+ messages)  latest in thread

Message-ID: <2657.981203@bo.nettuno.it>
Permalink:  ../2657.981203@bo.nettuno.it/
Also on:    postgresql.org/message-id/2657.981203@bo.nettuno.it

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: sferac@bo.nettuno.it, mjnf@uevora.pt
  Subject: Re: [SQL] Query to eliminate duplicates
  In-Reply-To: <2657.981203@bo.nettuno.it>
  References: <XFMail.981203085921.mjnf@uevora.pt> <2657.981203@bo.nettuno.it>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox