pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselecting duplicate records
4+ messages / 4 participants
[nested] [flat]
* selecting duplicate records
@ 2003-09-22 05:37 NAGAPPA <naveen_shanbhag@hotmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: NAGAPPA @ 2003-09-22 05:37 UTC (permalink / raw)
To: pgsql-sql
Hi
I would like to know the following
1. How to select duplicate records only from a single table using a select query.
Thanks in advance
Regards
naveen
----- Original Message -----
From: yogesh selukar
To: pgsql-sql@postgresql.org ; sandis@mediaparks.lv
Sent: Saturday, September 20, 2003 10:35 PM
Subject: Re: [SQL] Simple concatenation in select query
Hello Everybpdy,
The concatination in select query u can write the query like this.
select c.status||' this '||' is '||' the '||' status ' from course_master as c;
This works fine
Regards
Yogesh
------------------------------------------------------------------------------
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: selecting duplicate records
@ 2003-09-22 13:25 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 4+ messages in thread
From: Christoph Haller @ 2003-09-22 13:25 UTC (permalink / raw)
To: pgsql-sql; naveen_shanbhag@hotmail.com
> 1. How to select duplicate records only from a single table using a
select
> query.
>
e.g.
select sid,count(sid) from location group by sid having count(sid)>1;
Do you get the idea?
Your request is pretty unspecific, so if this is not what you're asking
for,
try again.
Regards, Christoph
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: selecting duplicate records
@ 2003-09-23 03:13 Christopher Browne <cbbrowne@acm.org>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 4+ messages in thread
From: Christopher Browne @ 2003-09-23 03:13 UTC (permalink / raw)
To: pgsql-sql
The world rejoiced as ch@rodos.fzk.de (Christoph Haller) wrote:
>> 1. How to select duplicate records only from a single table using a
> select
>> query.
>>
> e.g.
> select sid,count(sid) from location group by sid having count(sid)>1;
>
> Do you get the idea?
> Your request is pretty unspecific, so if this is not what you're asking
> for,
> try again.
The aggregate is likely to perform horrifically badly. Here might
be an option:
Step 1. Find all of the duplicates...
select a.* into temp table sid from some_table a, some_table b
where a.oid < b.oid and
a.field1 = b.field1 and
a.field2 = b.field2 and
a.field3 = b.field3 and
...
a.fieldn = b.fieldn;
Step 2. Look for the matching entries in the source table...
select a.* from some_table a, sid b
where
a.field1 = b.field1 and
a.field2 = b.field2 and
a.field3 = b.field3 and
...
a.fieldn = b.fieldn;
[There's a weakness here; if there are multiple dupes, they may get
picked multiple times in the second query :-(.]
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne; rate me
http://cbbrowne.com/info/rdbms.html
As Will Rogers would have said, "There is no such thing as a free
variable." -- Alan Perlis
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: selecting duplicate records
@ 2003-09-23 20:34 Guy Fraser <guy@incentre.net>
parent: Christopher Browne <cbbrowne@acm.org>
0 siblings, 0 replies; 4+ messages in thread
From: Guy Fraser @ 2003-09-23 20:34 UTC (permalink / raw)
To: pgsql-sql
This is a test I did on one of my tables where I put duplicated entries :
select *
from acct_other_2003sep
except select sep.*
from (
select min(oid) as min_oid,
"Acct-Status-Type",
"User-Name",
"Realm",
"Acct-Session-Time",
"Acct-Input-Octets",
"Acct-Output-Octets",
"Called-Station-Id",
"Calling-Station-Id",
"Acct-Terminate-Cause",
"Framed-IP-Address",
"Service-Type",
"Framed-Protocol",
"Client-IP-Address",
"NAS-IP-Address",
"NAS-Port-Type",
"NAS-Port-Id",
"Acct-Session-Id",
"Acct-Link-Count",
"Acct-Multi-Session-Id"
from acct_other_2003sep
group by "Acct-Status-Type",
"User-Name",
"Realm",
"Acct-Session-Time",
"Acct-Input-Octets",
"Acct-Output-Octets",
"Called-Station-Id",
"Calling-Station-Id",
"Acct-Terminate-Cause",
"Framed-IP-Address",
"Service-Type",
"Framed-Protocol",
"Client-IP-Address",
"NAS-IP-Address",
"NAS-Port-Type",
"NAS-Port-Id",
"Acct-Session-Id",
"Acct-Link-Count",
"Acct-Multi-Session-Id"
) as min_sep,
acct_other_2003sep as sep
where sep.oid = min_sep.min_oid
;
From the above example you can see how to use a subselect to get a
unique list then using except, you can get the records that were not unique.
This may not be exactly what you want but it does implement some of the
methods required to get around using using temporary tables.
For some tasks using temporary tables may be more suitable if your query
becomes too complex and or you run out of memory/time.
Hope this helps.
Guy
Christopher Browne wrote:
>The world rejoiced as ch@rodos.fzk.de (Christoph Haller) wrote:
>
>
>>>1. How to select duplicate records only from a single table using a
>>>
>>>
>>select
>>
>>
>>>query.
>>>
>>>
>>>
>>e.g.
>>select sid,count(sid) from location group by sid having count(sid)>1;
>>
>>Do you get the idea?
>>Your request is pretty unspecific, so if this is not what you're asking
>>for,
>>try again.
>>
>>
>
>The aggregate is likely to perform horrifically badly. Here might
>be an option:
>
>Step 1. Find all of the duplicates...
>
>select a.* into temp table sid from some_table a, some_table b
> where a.oid < b.oid and
> a.field1 = b.field1 and
> a.field2 = b.field2 and
> a.field3 = b.field3 and
> ...
> a.fieldn = b.fieldn;
>
>Step 2. Look for the matching entries in the source table...
>
>select a.* from some_table a, sid b
> where
> a.field1 = b.field1 and
> a.field2 = b.field2 and
> a.field3 = b.field3 and
> ...
> a.fieldn = b.fieldn;
>
>[There's a weakness here; if there are multiple dupes, they may get
>picked multiple times in the second query :-(.]
>
>
--
Guy Fraser
Network Administrator
The Internet Centre
780-450-6787 , 1-888-450-6787
There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-09-23 20:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-22 05:37 selecting duplicate records NAGAPPA <naveen_shanbhag@hotmail.com>
2003-09-22 13:25 Re: selecting duplicate records Christoph Haller <ch@rodos.fzk.de>
2003-09-23 03:13 ` Christopher Browne <cbbrowne@acm.org>
2003-09-23 20:34 ` Guy Fraser <guy@incentre.net>
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