Thread: Duplicate records

Duplicate records

From

"Ezequias Rodrigues da Rocha"

Date:

02 February 2007, 12:49:12

Hi list,<br /><br />I am making some data minning and would like to know if someone tell me how to retrieve the
duplicaterecords based in one or two fields of my table.<br /><br />Any help would be welcomed.<br clear="all" /><br
/>--<br /> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=<br
/>                                  Atenciosamente(Sincerely)<br />                        Ezequias Rodrigues da
Rocha<br/> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- <br />A pior das democracias ainda
émelhor do que a melhor das ditaduras<br />The worst of democracies is still better than the better of dictatorships<br
/><ahref="http://ezequiasrocha.blogspot.com/">http://ezequiasrocha.blogspot.com/ </a>

Re: Duplicate records

From

"Shoaib Mir"

Date:

02 February 2007, 12:55:20

That has been asked in the past a number of time too, you can look at the following for details --> http://archives.postgresql.org/pgsql-novice/2006-06/msg00093.php

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 2/2/07, Ezequias Rodrigues da Rocha < ezequias.rocha@gmail.com> wrote:

Hi list,

I am making some data minning and would like to know if someone tell me how to retrieve the duplicate records based in one or two fields of my table.

Any help would be welcomed.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

Re: Duplicate records

From

"Bart Degryse"

Date:

02 February 2007, 12:55:41

select *

from mytable A, (select field1, field2, count(*) from mytable group by field1, field2 having count(*) > 1) B

where A.field1 = B.field1 and A.field2 = B.field2

>>> "Ezequias Rodrigues da Rocha" <ezequias.rocha@gmail.com> 2007-02-02 14:48 >>>
Hi list,

I am making some data minning and would like to know if someone tell me how to retrieve the duplicate records based in one or two fields of my table.

Any help would be welcomed.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor d o que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

Re: Duplicate records

From

"Ezequias Rodrigues da Rocha"

Date:

02 February 2007, 12:59:41

Thank you but are you talking of two tables ? I intent to check in only one table.

Please explain to me.

Ezequias

2007/2/2, Bart Degryse < Bart.Degryse@indicator.be>:

select *
from mytable A, (select field1, field2, count(*) from mytable group by field1, field2 having count(*) > 1) B
where A.field1 = B.field1 and A.field2 = B.field2

>>> "Ezequias Rodrigues da Rocha" < ezequias.rocha@gmail.com> 2007-02-02 14:48 >>>

Hi list,

I am making some data minning and would like to know if someone tell me how to retrieve the duplicate records based in one or two fields of my table.

Any help would be welcomed.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

Re: Duplicate records

From

"Shoaib Mir"

Date:

02 February 2007, 13:03:35

Where do you see the second table in it?

I guess here:

A = mytable
B = (select field1, field2, count(*) from mytable group by field1, field2 having count(*) > 1)

So that is all around one table that is 'mytable', where A and B are just the aliases.

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 2/2/07, Ezequias Rodrigues da Rocha < ezequias.rocha@gmail.com> wrote:

Thank you but are you talking of two tables ? I intent to check in only one table.

Please explain to me.

Ezequias

2007/2/2, Bart Degryse < Bart.Degryse@indicator.be>:
select *
from mytable A, (select field1, field2, count(*) from mytable group by field1, field2 having count(*) > 1) B
where A.field1 = B.field1 and A.field2 = B.field2

>>> "Ezequias Rodrigues da Rocha" < ezequias.rocha@gmail.com> 2007-02-02 14:48 >>>

Hi list,

I am making some data minning and would like to know if someone tell me how to retrieve the duplicate records based in one or two fields of my table.

Any help would be welcomed.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                                  Atenciosamente (Sincerely)
                        Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                                  Atenciosamente (Sincerely)
                        Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

Re: Duplicate records

From

"Bart Degryse"

Date:

02 February 2007, 13:13:34

The only table in my query is "mytable".

The part (select field1, field2, count(*) from mytable group by field1, field2 having count(*) > 1)

counts how many records have the same field1 and field2 and returns a record per combination of field1 and field2 that have more than one (= duplicates)

The rest uses that information to filter all fields of all the records from that same table having such a combination of field1 and field2
This version is even a little shorter:

select *

from mytable A, (select field1, field2 from mytable group by field1, field2 having count(*) > 1) B

where A.field1 = B.field1 and A.field2 = B.field2

>>> "Ezequias Rodrigues da Rocha" <ezequias.rocha@gmail.com> 2007-02-02 14:59 >>>
Thank you but are you talking of two tables ? I intent to check in only one table.

Please explain to me.

Ezequias

2007/2/2, Bart Degryse < Bart.Degryse@indicator.be>:

select *
from mytable A, (select field1, field2, count(*) from mytable group by field1, field2 having count(*) > 1) B
where A.field1 = B.field1 and A.field2 = B.field2

>>> "Ezequias Rodrigues da Rocha" < ezequias.rocha@gmail.com> 2007-02-02 14:48 >>>

Hi list,

I am making some data minning and would like to know if someone tell me how to retrieve the duplicate records based in one or two fields of my table.

Any help would be welcomed.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

Re: Duplicate records

From

Mezei Zoltán

Date:

02 February 2007, 13:29:36

Ezequias Rodrigues da Rocha wrote:
> Hi list,
>
> I am making some data minning and would like to know if someone tell 
> me how to retrieve the duplicate records based in one or two fields of 
> my table.
>
> Any help would be welcomed.
You can use a simple query like this one:

select t1.id, t2.id
from table t1, table t2
where t1.record1 = t2.record1     and t1.record2 = t2.record2     and t1.id != t2.id

This one lists the ids of the matching records. You may need to fine 
tune it to your needs, e.g. use distinct or replace the != with <.

Zizi

Re: Duplicate records

From

"Ezequias Rodrigues da Rocha"

Date:

02 February 2007, 14:01:11

You are correct. Sorry my mistake. The SQL statement is correct and I find the duplicate records.

ps: I just think postresql could make this easyly. Don't you think ? Any function or anything else.

Thank you so much.

Ezequias

2007/2/2, Shoaib Mir <shoaibmir@gmail.com >:

Where do you see the second table in it?

I guess here:

A = mytable
B = (select field1, field2, count(*) from mytable group by field1, field2 having count(*) > 1)

So that is all around one table that is 'mytable', where A and B are just the aliases.

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 2/2/07, Ezequias Rodrigues da Rocha < ezequias.rocha@gmail.com> wrote:
Thank you but are you talking of two tables ? I intent to check in only one table.

Please explain to me.

Ezequias

2007/2/2, Bart Degryse < Bart.Degryse@indicator.be>:
select *
from mytable A, (select field1, field2, count(*) from mytable group by field1, field2 having count(*) > 1) B
where A.field1 = B.field1 and A.field2 = B.field2

>>> "Ezequias Rodrigues da Rocha" < ezequias.rocha@gmail.com> 2007-02-02 14:48 >>>

Hi list,

I am making some data minning and would like to know if someone tell me how to retrieve the duplicate records based in one or two fields of my table.

Any help would be welcomed.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                                  Atenciosamente (Sincerely)
                        Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
                                  Atenciosamente (Sincerely)
                        Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

Re: Duplicate records

From

Andrew Sullivan

Date:

02 February 2007, 14:17:03

> 
> ps: I just think postresql could make this easyly. Don't you think ? Any
> function or anything else.

What's hard about the self-join?  That's how SQL works.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
When my information changes, I alter my conclusions.  What do you do sir?    --attr. John Maynard Keynes