Re: Finding rows with column1+column2 values are unique

qazmlp1209_at_rediffmail.com wrote:
> qazmlp1209_at_rediffmail.com wrote:
> > How do we find out the number of rows in a Table where the combination
> > of two (non-key)column values are unique?
>
> I have put my requirement wrongly. Here is the correct one?
>
> COLUMN1 is a key column.
> COLUMN2, COLUMN3 are the non-key columns.
>
> -COLUMN1- COLUMN2 COLUMN3
> ---------- --------- --------
> 1 10 S1
> 1 10 S2
> 2 20 S3
> 2 30 S4
> 3 40 S5
>
>
> We have to find out the number of COLUMN1s, where there is a 1:1
> relation between all the COLUMN2 and COLUMN3 values for a given COLUMN1
> value.
> For example, for the above table, it should result in the following:
> 2 20 S3
> 2 30 S4
> 3 40 S5
>
> The count is 3 rows.
>
> How exactly it can be done using SQL statement?

select column1,column2,column3 from thistable group by column1,column2,column3
having count(*)=1;

Dealing with NULL is left as an exercise for the reader (Hint: NVL() function)

HTH,
ed Received on Wed Jan 17 2007 - 10:46:34 CST