CODEWITHSUNDEEP
sqloracle-database
kume8sit
kume8sit

Reputation: 137

SQL/Oracle return only field with identical value in 2nd column

Need to return column 1 only if identical values are found in 2nd column of a repeating log. If any other value is seen exclude from result.

A   2
A   2
A   2
A   2
A   2

Exlude

B   2
B   1
B   2
B   3
B   2


 select b. column1 
    from 
    ( select * 
      from table 
      where column2 != 1

    ) b
  where b.column2 = 2

Results: 

A

Upvotes: 0

Views: 49

Answers (3)

Indrakumara
Indrakumara

Reputation: 1645

Try this query 

select column1 from (select column1,column2 from Test group by column1,column2) a group by column1 having count(column1)=1;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

If you need the original rows, I would recommend not exists:

select t.*
from t
where not exists (select 1 from t t2 where t2.col1 = t.col1 and t2.col2 <> t.col2);

If you just want the col1 values (which makes sense to me), then I would phrase the aggregation as:

select col1
from t
group by col1
having min(col2) = max(col2);

If you want to include "all-null" as a valid option, then:

having min(col2) = max(col2) or min(col2) is null

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

You could use aggregation and HAVING:

SELECT col1
FROM tab
GROUP BY col1
HAVING COUNT(DISTINCT col2) = 1;

or if you need original rows:

SELECT s.*
FROM (SELECT t.*, COUNT(DISTINCT col2) OVER(PARTITION BY col1) AS cnt
      FROM tab t) s
WHERE s.cnt = 1;

Upvotes: 2

Related Questions