AW: Ad-hoc report of duplicates using SQL or Query

Why to concatenate f1 and f2? The query will result in a table scan!

Select x.*
from mytable x
where (x.f1, x.f2) in (select f1, f2 from mytable group by f1, f2 having
count(*) > 1)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Mark Walter
Gesendet: Saturday, 11. February 2012 19:03
An: Midrange Systems Technical Discussion
Betreff: RE: Ad-hoc report of duplicates using SQL or Query

The first one is easy.

Select * from mytable where f1 || f2 in (select f1 || f2 as myfield from
mytable group by myfield having count(*) > 1)

I'm not sure I understand the second requirement.



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vinay Gavankar
Sent: Saturday, February 11, 2012 12:59 PM
To: Midrange Systems Technical Discussion
Subject: Ad-hoc report of duplicates using SQL or Query

Hi,

I have a file with 6 fields say F1 thru F6.

I want to find all the records in the file where field F1 and F2 is same.

I also want another report which has all the records where Field F1 is same
but F2 is different.

I know that I have stated the requirements badly, so let me illustrate with
an example.

If my file has following records denoted by F1,F2,F3,F4,F5,F6 (the
combination of F3,F4,F5,F6 is different in all the records):

AAA,XXX,*,*,*,*
AAA,YYY,*,*,*,*
BBB,XXX,*,*,*,*
CCC,XXX,*,*,*,*
DDD,YYY,*,*,*,*
DDD,YYY,*,*,*,*
EEE,XXX,*,*,*,*
EEE,ZZZ,*,*,*,*
FFF,XXX,*,*,*,*
FFF,XXX,*,*,*,*
FFF,XXX,*,*,*,*
FFF,ZZZ,*,*,*,*

My first report will have records:
DDD,YYY,*,*,*,*
DDD,YYY,*,*,*,*
FFF,XXX,*,*,*,*
FFF,XXX,*,*,*,*
FFF,XXX,*,*,*,*

My second report will have records:
AAA,XXX,*,*,*,*
AAA,YYY,*,*,*,*
EEE,XXX,*,*,*,*
EEE,ZZZ,*,*,*,*
FFF,XXX,*,*,*,*
FFF,XXX,*,*,*,*
FFF,XXX,*,*,*,*
FFF,ZZZ,*,*,*,*

To further clarify, F1 is something called PA#, F2 is a Member Id. In the
file, PA# was "supposed" to be unique, but it is not. So I want to identify
all records where same PA# is repeated for the Same member and in another
report, where same PA# is repeated for different members.

Is it possible using only SQL and/or query. Creating a temporary
intermediate file is OK too.

Any help will be really appreciated.

TIA
Vinay