Sergey,
Thanks, this look like the right direction. What's the sqrt about at the
bottom? I need to have the SQL parsable in both Oracle and UDB, so I need
to understand it a little better. Thanks for all of your help.
"Sergey Adamenko" <adamenko_at_i.com.ua> wrote in message
news:akbakd$2929$1_at_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 - 19:02:31 CDT
