thanks for your answer ,
i could not make a test now ,

...just a dubt i have :
is it valid for a different rows number too ?
in my case the number of the row is variable .

Maurizio

----- Original Message -----
From: Svein Erling Tysvaer
To: Firebird-support
Sent: Tuesday, September 05, 2006 9:03 AM
Subject: Re: [firebird-support] sql question


Hi again, now I'm awake and my suggested solution needs some minor
corrections:

select distinct M1.ID, M2.ID
/*Distinct to avoid duplicates*/
from MaurizioTable M1
join MaurizioTable M2 on M2.F1 = M1.F1
and M2.F2 = M1.F2
and M2.F3 = M1.F3
and M2.ID+0 > M1.ID
/*Clause added to avoid lots of cases where M1.ID = M2.ID*/
/*I also expect one row (10, 16) to be enough*/
/*and that you do not need (16, 10) as well*/
/*+0 added due to expected speed improvement*/
where not exists(select * from MaurizioTable M3
where M3.ID = M1.ID
where not exists(select * from MaurizioTable M4
where M4.ID = M2.ID
and M4.F1 = M3.F1
and M4.F2 = M3.F2
and M4.F3 = M3.F3))
/*There should be no M1.ID that does not have a duplicate in M2*/
and not exists(select * from MaurizioTable M5
where M5.ID = M2.ID
where not exists(select * from MaurizioTable M6
where M6.ID = M1.ID
and M6.F1 = M5.F1
and M6.F2 = M5.F2
and M6.F3 = M5.F3))
/*There should be no M2.ID that does not have a duplicate in M1*/

Though I still haven't tried parsing this and suspect that the size of
the table as well as index selectivity may or may not make this a viable
solution (it will be slow on a table with millions of rows)...

Set

Svein Erling Tysvaer wrote:
> Hi Maurizio!
>
> I'd say your question is very much an on-topic subject for this list.
> Here's my five minute attempt for a solution:
>
> select M1.ID, M2.ID
> from MaurizioTable M1
> join MaurizioTable M2 on M2.F1 = M1.F1
> and M2.F2 = M1.F2
> and M2.F3 = M1.F3
> where not exists(select * from MaurizioTable M3
> where M3.ID = M1.ID
> where not exists(select * from MaurizioTable M4
> where M4.ID = M2.ID
> and M4.F1 = M3.F1
> and M4.F2 = M3.F2
> and M4.F3 = M3.F3))
> and not exists(select * from MaurizioTable M5
> where M5.ID = M2.ID
> where not exists(select * from MaurizioTable M6
> where M6.ID = M1.ID
> and M6.F1 = M5.F1
> and M6.F2 = M5.F2
> and M6.F3 = M5.F3))
>
> Now, it's way past bedtime and I wrote this using ThunderBird, which is
> lousy at parsing SQL ;o). Hence, no guarantees that I'm even close to a
> working solution, but
>
> HTH,
> Set
>
> Maurizio P. wrote:
>> hi
>> i know it is not connected with the newsgroup theme ,
>> but i would be glad for any suggestion :
>>
>> how can i find if there are two groups with the same
>> values , where *ID* is the index of the group ?
>>
>> for example in the case :
>>
>> ID F1 F2 F3
>>
>> 5 D F G
>> 5 S J T
>> 10 A C H
>> 10 Z J H
>> 12 A C F
>> 12 A C X
>> 15 D D G
>> 16 A C H
>> 16 Z J H
>>
>> i have a duplicate with the ID 10 and 16
>> infact :
>>
>> 10 A C H
>> 10 Z J H
>>
>> are the same of :
>> 16 A C H
>> 16 Z J H
>>
>>
>> ..mmm... maybe my knowledge of SQL is still poor ,
>> but today i could not find a good solution .
>>
>> thanks
>>
>> bye
>>
>> Maurizio





------------------------------------------------------------------------------


No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.11.7/437 - Release Date: 04/09/2006


[Non-text portions of this message have been removed]