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

Hi,

@ 1: List all rows with duplicate keys
With x as (Select Key1, Key2
from YourTable
Group By Key1, Key2
Having count(*) > 1)
Select y.*

From x join YourTable y

on x.Key1 = y.Key1 and x.Key2 = y.Key2
Order By x.Key1, x.Key2
;;

@ 2: List all rows without duplicate keys:
With x as (Select Key1, Key2
from YourTable
Group By Key1, Key2
Having count(*) = 1)
Select y.*

From x join YourTable y

on x.Key1 = y.Key1 and x.Key2 = y.Key2
Order By x.Key1, x.Key2


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 Vinay Gavankar
Gesendet: Saturday, 11. February 2012 18:59
An: Midrange Systems Technical Discussion
Betreff: 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