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?

This is table a with columns a,b and c.

select * from (select a,b,c from a where (a,b) in(select distinct a,b from (select a,b,count(1) from a group by a,b having count(1)<2))); Received on Wed Jan 17 2007 - 00:20:54 CST