Re: to all SQL experts...

Hi Sergey,

thank you for your check. I gave a wrong answer. What about

select t1.col1, x.col1t as col2
from
(
select t1.col1, count (*) as t1num
from testtab t1
group by t1.col1
) t1
, (
select t1.col1, t2.col1 as col1t, count (*) as xnum from testtab t1, testtab t2
where t1.col2 = t2.col2
and t1.col1 < t2.col1
group by t1.col1, t2.col1
) x
where t1.col1 = x.col1
and t1.t1num = x.xnum
order by 1, 2
/

At least this gives the correct results for your testcase and the original one. And now the logic behind it seems correct.

Martin

Sergey Adamenko wrote:
>
> "Martin Haltmayer" <Martin.Haltmayer_at_d2mail.de> ???????/???????? ? ????????
> ?????????: news:3D69D74A.16A006A8_at_d2mail.de...
> > Assumed you have (col1, col2) as a primary key on your testtab the following
> > worked for me:
> >
> > select distinct dt1.col1, dt2.col1
> > from
> > (
> > select t1.col1, count (*) as num_col2
> > from testtab t1
> > group by t1.col1
> > ) dt1
> > ,
> > (
> > select t1.col1 as col1a, count (t2.col1) as num_col1b
> > from testtab t1, testtab t2
> > where t1.col1 != t2.col1
> > and t1.col2 = t2.col2
> > group by t1.col1
> > ) da1
> > ,
> > (
> > select count (t1.col1) as num_col1a, t2.col1 as col1b
> > from testtab t1, testtab t2
> > where t1.col1 != t2.col1
> > and t1.col2 = t2.col2
> > group by t2.col1
> > ) db1
> > ,
> > (
> > select t1.col1, count (*) as num_col2
> > from testtab t1
> > group by t1.col1
> > ) dt2
> > where dt1.col1 = da1.col1a
> > and da1.num_col1b = db1.num_col1a
> > and db1.col1b = dt2.col1
> > and dt1.col1 < dt2.col1
> > order by 1, 2
> > /
> >
> > Regards,
> >
> > Martin
>
> Something is definitly wrong. I tried the following query and it gives
> unpredictable results:
>
> SQL>
> SQL> DROP TABLE TESTTAB CASCADE CONSTRAINTS;
>
> Table dropped.
>
> SQL>
> SQL> CREATE TABLE TESTTAB (
> 2 COL1 NUMBER (5) NOT NULL,
> 3 COL2 NUMBER (5) NOT NULL,
> 4 PRIMARY KEY ( COL1, COL2 ) ) ;
>
> Table created.
>
> SQL>
> SQL> Insert into testtab
> 2 select 1,15 from dual
> 3 union select 1,35 from dual
> 4 union select 1,46 from dual
> 5 union select 1,72 from dual
> 6 union select 2,10 from dual
> 7 union select 2,35 from dual
> 8 union select 2,46 from dual
> 9 union select 2,11 from dual
> 10 union select 2,19 from dual
> 11 union select 2,22 from dual
> 12 union select 3,15 from dual
> 13 union select 3,35 from dual
> 14 union select 3,46 from dual
> 15 union select 3,72 from dual
> 16 union select 4,11 from dual
> 17 union select 4,25 from dual
> 18 union select 5,110 from dual
> 19 union select 5,135 from dual
> 20 union select 5,146 from dual
> 21 union select 5,111 from dual
> 22 union select 5,119 from dual
> 23 union select 5,22 from dual;
>
> 22 rows created.
>
> SQL>
> SQL>
> SQL> select distinct dt1.col1, dt2.col1
> 2 from
> 3 (
> 4 select t1.col1, count (*) as num_col2
> 5 from testtab t1
> 6 group by t1.col1
> 7 ) dt1
> 8 ,
> 9 (
> 10 select t1.col1 as col1a, count (t2.col1) as num_col1b
> 11 from testtab t1, testtab t2
> 12 where t1.col1 != t2.col1
> 13 and t1.col2 = t2.col2
> 14 group by t1.col1
> 15 ) da1
> 16 ,
> 17 (
> 18 select count (t1.col1) as num_col1a, t2.col1 as col1b
> 19 from testtab t1, testtab t2
> 20 where t1.col1 != t2.col1
> 21 and t1.col2 = t2.col2
> 22 group by t2.col1
> 23 ) db1
> 24 ,
> 25 (
> 26 select t1.col1, count (*) as num_col2
> 27 from testtab t1
> 28 group by t1.col1
> 29 ) dt2
> 30 where dt1.col1 = da1.col1a
> 31 and da1.num_col1b = db1.num_col1a
> 32 and db1.col1b = dt2.col1
> 33 and dt1.col1 < dt2.col1
> 34 order by 1, 2;
>
> COL1 COL1
> ---------- ----------
> 1 2
> 1 3
> 2 3
> 4 5
>
> SQL>
> SQL>
>
> Best regards,
> Sergey Adamenko.
Received on Mon Aug 26 2002 - 06:04:00 CDT