Re: SQL query!

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 17 Jan 2007 04:04:09 -0800
Message-ID: <1169035449.890208.298330@v45g2000cwv.googlegroups.com>

qazmlp1209_at_rediffmail.com wrote:
> (Please ignore my previous thread.)
> I have considered all the requirements below. Please help in
> formulating an SQL for it. Thanks!
>
> I have a Table with the columns: Column-a, Column-b,
> Column-c,Column-d.....Column-z.
> But, for the below problem, we can ignore the values in the column-d,
> .....,Column-z.
>
> I would like to form an SQL selecting the distinct values of the
> Column-a where
> - Column-c is NOT NULL
> - There is more than 1 distinct association between Column-a, Column-b
> values, and
> Column-b values have 1:1 association with the Column-c values in
> that rows.
>
> E.g.
> Column-a Column-b Column-c
> -------------- --------- --------
> 1 10 S1
> 1 10 S2
> 2 20 S3
> 2 30 S4
> 3 40 S5
> 4 50 (NULL)
>
> Here,
> - '1' should not be selected, as there is only one association exists
> with Column-b: 1,10
> - '2' should be selected, as there are multiple associations exists
> i.e. 2,20 & 2,30 and 20, 30 have distinct values in Column-c.
> - '3' should not be selected, as there is only association exists with
> Column-b
> - '4' should not be selected, as there is a NULL in column 'c'
>
> How do we achieve it in the SQL?

The setup:
CREATE TABLE T1 (

  COLUMN_A NUMBER(10),
  COLUMN_B NUMBER(10),
  COLUMN_C VARCHAR2(10));

INSERT INTO T1 VALUES (1,10,'S1');
INSERT INTO T1 VALUES (1,10,'S2');
INSERT INTO T1 VALUES (2,20,'S3');
INSERT INTO T1 VALUES (2,30,'S4');

INSERT INTO T1 VALUES (3,40,'S5');
INSERT INTO T1 VALUES (4,50,NULL); Trying to find usable statistics for the data using analytical functions:
SELECT

  COLUMN_A,
  COLUMN_B,
  COLUMN_C,

  COUNT(*) OVER (PARTITION BY COLUMN_A, COLUMN_B) NON_DISTINCT_COLUMN_C,
  COUNT(COLUMN_C) OVER (PARTITION BY COLUMN_A, COLUMN_B) DISTINCT_COLUMN_C,
  COUNT(*) OVER (PARTITION BY COLUMN_A) NON_DISTINCT_COLUMN_B_C,   ROW_NUMBER() OVER (PARTITION BY COLUMN_A, COLUMN_B ORDER BY COLUMN_C) ROWNUM_COLUMN_C
FROM
  T1
ORDER BY
  COLUMN_A,
  COLUMN_B; C_A C_B C_C N_D_COLUMN_C D_COLUMN_C N_D_COLUMN_B_C R_COLUMN_C

  1   10  S1              2          2              2          1
  1   10  S2              2          2              2          2
  2   20  S3              1          1              2          1
  2   30  S4              1          1              2          1
  3   40  S5              1          1              1          1
  4   50                  1          0              1          1

Looking over the patterns in the above, we should eliminate all values of COLUMN_A where any DISTINCT_COLUMN_C is less than 1, and also those values of COLUMN_A where any DISTINCT_COLUMN_C is greater than 1. We also want to make certain that there is more than one row for each COLUMN_A value, so we need to make certain that the smallest NON_DISTINCT_COLUMN_B_C is greater than 1. If we slide the above into an inline view, we can accomplish these restrictions by using a GROUP BY and HAVING clause:
SELECT
  COLUMN_A
FROM
  (SELECT

    COLUMN_A,
    COLUMN_B,
    COLUMN_C,

    COUNT(*) OVER (PARTITION BY COLUMN_A, COLUMN_B) NON_DISTINCT_COLUMN_C,
    COUNT(COLUMN_C) OVER (PARTITION BY COLUMN_A, COLUMN_B) DISTINCT_COLUMN_C,
    COUNT(*) OVER (PARTITION BY COLUMN_A) NON_DISTINCT_COLUMN_B_C,     ROW_NUMBER() OVER (PARTITION BY COLUMN_A, COLUMN_B ORDER BY COLUMN_C) ROWNUM_COLUMN_C
  FROM
    T1)
GROUP BY
  COLUMN_A
HAVING
  MIN(DISTINCT_COLUMN_C)=1
  AND MAX(DISTINCT_COLUMN_C)=1
  AND MIN(NON_DISTINCT_COLUMN_B_C)>1
ORDER BY
  COLUMN_A; COLUMN_A
       2

Note that in the above, we are requesting that Oracle perform work that is unnecessary - you should determine what is unnecessary work are remove those analytical functions.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Jan 17 2007 - 06:04:09 CST