CODEWITHSUNDEEP
sqloracle-database
vinod kumar
vinod kumar

Reputation: 175

Query to get the rows in a table where value in column 1 is mapped to exactly one value in column 2

I have a table T1 like below:

COL1  COL2                   
---------------
aaa   10 
bbb   20                     
bbb   20                     
bbb   10                     
ccc   30                     
ccc   30                     
aaa   30                     
ddd   30

I want col1 and col2 values where col1 is mapped to only one col2.

COL1 COL2                   
-----------
ccc 30                     
ddd 30

Please let me know how to achieve my goal.

I tried with the following to get required result set:

select distinct col1, col2 
 from t1 
where col1 in (select col1 
                 from (select distinct col1, col2 from t1) 
                group by col1 
               having count(col2) = 1);

What are the other options without having those many inner queries.

Thanks In Advance.

Upvotes: 0

Views: 60

Answers (2)

Andomar
Andomar

Reputation: 238296

select  Col1
,       max(Col2) as Col2
from    YourTable
group by
        Col1
having  count(distinct Col2) = 1

The having clause makes sure there's only one Col2 in a single group. You can display it using max, min or even avg.

See it working at SQL Fiddle (thanks to Amit Singh.)

Upvotes: 2

Amit Singh
Amit Singh

Reputation: 8129

Select Distinct A.Col1,A.Col2 from Table1 A
inner join 
(Select Col1,Count(Distinct Col2) as col3 from Table1 group by Col1) B on
A.Col1=B.Col1 and B.Col3=1

Sql Fiddle

Upvotes: 1

Related Questions