Re: to all SQL experts...

From: Sergey Adamenko <adamenko_at_i.com.ua>
Date: Mon, 26 Aug 2002 12:33:30 +0300
Message-ID: <akcspm$12bo$1@news.dg.net.ua>

"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 - 04:33:30 CDT