× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.