Re: Finding rows with column1+column2 values are unique

Steve Robin wrote:
> 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)));

How exactly we can modify the query considering the following:

• to ignore the rows where there are not more than 1 associations between column-a, column-b
• to ignore the rows where the column-c is having null values ??