Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: to all SQL experts...

Re: to all SQL experts...

From: D.Y. <dyou98_at_aol.com>
Date: 26 Aug 2002 10:16:30 -0700
Message-ID: <f369a0eb.0208260916.2fc98edf@posting.google.com>


Got to admit that I haven't seen a smart query like this in a while. That sum(sqrt) with group by is tricky. Congratulations. This query should work unless there are duplicates in some matching groups but not the others.

"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...
...
>
> 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 Mon Aug 26 2002 - 12:16:30 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US