Re: Complicated query: searching for results within results.

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 17 Nov 2006 19:39:24 -0800
Message-ID: <1163821164.335113.146550@e3g2000cwe.googlegroups.com>

phreaker wrote:
> Hi, I have a query that I need to do that I've been thinking about, and
> I am stuck at this point.
>
> Table 1 has columns A, B, and C
>
> For a given A, I need to figure out whether there exists <B,C> pairs
> that have the same B, but different C. For a given A, there are a
> potentially many Bs and many Cs.
>
>
> So for a given A, if there are these results in the table:
>
> B C
> 1 5
> 1 100
> 2 4
> 2 4
>
> I need to return true, since <1,5> and <1,100> have same B, but
> different C.
>
> Thanks!
> David

I am not sure that fully understand what you are trying to do, so I will provide a couple possibilities to you: CREATE TABLE TABLE_1 (

  A NUMBER(12),
  B NUMBER(12),
  C NUMBER(12));

INSERT INTO
  TABLE_1
VALUES (
  1,
  1,
  5);

INSERT INTO
  TABLE_1
VALUES (
  1,
  1,
  100);

INSERT INTO
  TABLE_1
VALUES (
  1,
  2,
  4);

INSERT INTO
  TABLE_1
VALUES (
  1,
  2,
  4);

INSERT INTO
  TABLE_1
VALUES (
  2,
  1,
  5);

INSERT INTO
  TABLE_1
VALUES (
  2,
  1,
  100);

A B C
 1 1 5
 1 1 100
 1 2 4
 1 2 4
 2 1 100
 2 1 5

If for a given value of A, I only want to retrieve those rows for which there are no duplicates, I can do something like this: SELECT

  A,
  B,
  C,

  COUNT(*) OVER (PARTITION BY A,B,C) FOUND_IN_ROWS FROM
  TABLE_1; A B C FOUND_IN_ROWS
 1 1 5 1
 1 1 100 1
 1 2 4 2
 1 2 4 2
 2 1 100 1
 2 1 5 1

Then, slide the above into an inline view to remove those rows where FOUND_IN_ROWS was greater than 1:
SELECT
  A,
  B,
  C
FROM
  (SELECT

    A,
    B,
    C,

    SIGN(COUNT(*) OVER (PARTITION BY A,B,C)-1) REPEATED   FROM
    TABLE_1)
WHERE
  REPEATED=0; A B C
 1 1 5
 1 1 100
 2 1 100
 2 1 5

If you only wanted to return TRUE because there were a couple non-duplicated rows for a value of A, we need additional data in the table to cause nothing but duplicates where A=2: INSERT INTO
  TABLE_1
VALUES (
  2,
  1,
  5);

INSERT INTO
  TABLE_1
VALUES (
  2,
  1,
  100);

We then use the same starting point:
SELECT

  A,
  B,
  C,

  COUNT(*) OVER (PARTITION BY A,B,C) FOUND_IN_ROWS FROM
  TABLE_1; A B C FOUND_IN_ROWS
 1 1 5 1
 1 1 100 1
 1 2 4 2
 1 2 4 2
 2 1 100 1
 2 1 5 1
 2 1 100 2
 2 1 5 2

And then modify it slightly to subtract 1 from the count, find the sign of the result and take 1 and subtract the previous result. The non-duplicated rows will now show 1, while the duplicated rows will show 0:
SELECT

  A,
  B,
  C,

  1-SIGN(COUNT(*) OVER (PARTITION BY A,B,C)-1) UNIQUE_VALUES_FOUND FROM
  TABLE_1; A B C FOUND_IN_ROWS
 1 1 5 1
 1 1 100 1
 1 2 4 0
 1 2 4 0
 2 1 100 0
 2 1 5 0
 2 1 100 0
 2 1 5 0

If we again slide the above into an inline view, group by A and find the sum - if the sum is greater than 0, return TRUE, otherwise return FALSE:
SELECT
  A,
  DECODE(SIGN(SUM(UNIQUE_VALUES_FOUND)),1,'TRUE','FALSE') UNIQUES_FOUND FROM
  (SELECT

    A,
    B,
    C,

    1-SIGN(COUNT(*) OVER (PARTITION BY A,B,C)-1) UNIQUE_VALUES_FOUND   FROM
    TABLE_1)
GROUP BY
  A;

A UNIQUES_FOUND
 1 TRUE
 2 FALSE Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Nov 17 2006 - 21:39:24 CST