Re: to all SQL experts...

From: Sergey Adamenko <adamenko_at_i.com.ua>
Date: Sun, 25 Aug 2002 22:13:07 +0300
Message-ID: <akbakd$2929$1@news.dg.net.ua>

"Chris Aiello" <caiell02_at_comcast.net> сообщил/сообщила в новостях следующее: news:JOB99.155973$2p2.7088303_at_bin4.nnrp.aus1.giganews.com...
> I am stumped, although that's not really hard....
>
> Here is my predicament:
>
> I have a table that has 2 columns, column 1 repeats values and column 2 can
> have any value. Here's an example
>
> col1 col2
> 1 15
> 1 35
> 1 46
> 1 72
> 2 10
> 2 35
> 2 46
> 2 11
> 2 19
> 2 22
> 3 15
> 3 35
> 3 46
> 3 72
> 4 11
> 4 25
>
> Here's the dilemna...
> I need a query that can find distinct col1 values that have matching col2
> entries, along with the same number of entries. For example col1 value '1'
> has 4 entries with '15','35','46',and '72'. col1 value 2 has the entries
> 10,35,46,11,19 and 22. value 3 has 15,35,46, and 72. col1 values 1 and 3
> have identical corresponding entries...the same values and the same number
> of entries. Can anyone help me out with this??
> thanks, Chris.
>
>

Well, if I correctly understand the problem, the SQL bellow show work

 SQL*Plus: Release 8.1.7.0.0 - Production on Нд. Срп 25 22:06:13 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production JServer Release 8.1.7.0.0 - Production

DROP TABLE PROB; Table dropped.

SQL>
SQL> CREATE TABLE PROB (

  2     COL1  NUMBER (5),
  3     COL2  NUMBER (5));

Table created.

SQL>
SQL>
SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 1, 15);

1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 1, 35); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 1, 46); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 1, 72); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 1, 46); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 1, 72); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 2, 10); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 2, 35); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 2, 46); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 2, 11); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 2, 19); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 2, 22); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 3, 15); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 3, 35); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 3, 46); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 3, 72); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 3, 46); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 3, 72); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 4, 11); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 4, 25); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 5, 150); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 5, 350); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 5, 46); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 5, 72); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 5, 46); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 5, 72); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 6, 15); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 6, 35); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 6, 460); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 6, 720); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 6, 46); 1 row created.

SQL> INSERT INTO PROB ( COL1, COL2 ) VALUES ( 6, 72); 1 row created.

SQL> Commit;

Commit complete.

SQL>
SQL> select
  2 prob1.col1 match1, prob2.col1 match2   3 from

  4     (select
  5        col1,
  6     count(*) master_count
  7      from
  8        prob group by col1) prob1,
  9     (select
 10        col1,
 11     count(*) master_count
 12      from
 13        prob group by col1) prob2

 14 where
 15 prob1.col1<prob2.col1
 16 and prob1.master_count = prob2.master_count  17 and prob1.master_count =

 18                            (select
 19                               sum(sqrt(count(*)))
 20                             from prob p1, prob p2
 21                              where
 22                                p1.col1 = prob1.col1
 23                                and p2.col1 = prob2.col1
 24                                and p1.col2 = p2.col2
 25         group by   p1.col2
 26                             );

    MATCH1     MATCH2
---------- ----------
         1          3

Sergey Adamenko Received on Sun Aug 25 2002 - 14:13:07 CDT