Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL query!
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');
COLUMN_A, COLUMN_B, 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,
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
![]() |
![]() |