Re: to all SQL experts...

"Chris Aiello" <caiell02_at_comcast.net> сообщил/сообщила в новостях следующее: news:reea9.207523$SS.8331734_at_bin3.nnrp.aus1.giganews.com...
> 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.

Hi, Chris.
SQRT is the common square root function, we all know from the math :)

The sub query bellow counts matches between GROUP_1 and GROUP_2 if groups contain unique numbers. (That is, you can issue CREATE UNIQUE INDEX U1 ON prob(col1, col2)):

        SELECT
           COUNT(*)
        FROM prob p1, prob p2
        WHERE
            p1.col1 = GROUP_1
            AND p2.col1 = GROUP_2
            AND p1.col2 = p2.col2

Things go worse if groups can contain dups. With SQL you can't count the matches between two groups because of Cartesian product effect. But the second sub query being compared to count of rows in the group will show if the group are totally equal:

        SELECT
           SUM(SQRT(COUNT(*)))
        FROM prob p1, prob p2
        WHERE
            p1.col1 = GROUP_1
            AND p2.col1 = GROUP_2
            AND p1.col2 = p2.col2
        GROUP BY   p1.col2

What is UDB? Anyway, there is a solution, so you have to modify it to other RDMS dialect.

Best regards,
Sergey Adamenko. Received on Mon Aug 26 2002 - 02:56:03 CDT