pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDuplicate records
9+ messages / 5 participants
[nested] [flat]
* Duplicate records
@ 2007-02-02 13:48 Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
0 siblings, 4 replies; 9+ messages in thread
From: Ezequias Rodrigues da Rocha @ 2007-02-02 13:48 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Duplicate records
@ 2007-02-02 13:54 Mezei Zoltán <mezei.zoltan@telefor.hu>
parent: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
3 siblings, 0 replies; 9+ messages in thread
From: Mezei Zoltán @ 2007-02-02 13:54 UTC (permalink / raw)
To: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Duplicate records
@ 2007-02-02 13:55 Shoaib Mir <shoaibmir@gmail.com>
parent: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
3 siblings, 0 replies; 9+ messages in thread
From: Shoaib Mir @ 2007-02-02 13:55 UTC (permalink / raw)
To: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Duplicate records
@ 2007-02-02 13:55 Bart Degryse <Bart.Degryse@indicator.be>
parent: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
3 siblings, 1 reply; 9+ messages in thread
From: Bart Degryse @ 2007-02-02 13:55 UTC (permalink / raw)
To: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>; pgsql-sql
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Duplicate records
@ 2007-02-02 13:59 Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
parent: Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 1 reply; 9+ messages in thread
From: Ezequias Rodrigues da Rocha @ 2007-02-02 13:59 UTC (permalink / raw)
To: Bart Degryse <Bart.Degryse@indicator.be>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Duplicate records
@ 2007-02-02 14:03 Shoaib Mir <shoaibmir@gmail.com>
parent: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Shoaib Mir @ 2007-02-02 14:03 UTC (permalink / raw)
To: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>; +Cc: Bart Degryse <Bart.Degryse@indicator.be>; pgsql-sql
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Duplicate records
@ 2007-02-02 14:13 Bart Degryse <Bart.Degryse@indicator.be>
parent: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
3 siblings, 0 replies; 9+ messages in thread
From: Bart Degryse @ 2007-02-02 14:13 UTC (permalink / raw)
To: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>; pgsql-sql
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 (
mailto: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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Duplicate records
@ 2007-02-02 15:00 Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
parent: Shoaib Mir <shoaibmir@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Ezequias Rodrigues da Rocha @ 2007-02-02 15:00 UTC (permalink / raw)
To: Shoaib Mir <shoaibmir@gmail.com>; +Cc: Bart Degryse <Bart.Degryse@indicator.be>; pgsql-sql
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/
>
>
>
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Duplicate records
@ 2007-02-02 15:16 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Andrew Sullivan @ 2007-02-02 15:16 UTC (permalink / raw)
To: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-02-02 15:16 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-02 13:48 Duplicate records Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2007-02-02 13:54 ` Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-02-02 13:55 ` Shoaib Mir <shoaibmir@gmail.com>
2007-02-02 13:55 ` Bart Degryse <Bart.Degryse@indicator.be>
2007-02-02 13:59 ` Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2007-02-02 14:03 ` Shoaib Mir <shoaibmir@gmail.com>
2007-02-02 15:00 ` Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
2007-02-02 15:16 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-02-02 14:13 ` Bart Degryse <Bart.Degryse@indicator.be>
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