CODEWITHSUNDEEP
sqloracle-database
Naveen Kumar
Naveen Kumar

Reputation: 79

Oracle query to retrieve distinct and common column value based on another column

enter image description here

I need to retrieve distinct AHQ-WS1 value FROM STYPE column where SCODE is having both 9531 AND 9569.

Basically common STYPE value of two different SCODE values but also being distinct.

Is it possible with only query or need java code seperately?

Upvotes: 0

Views: 58

Answers (2)

MT0
MT0

Reputation: 167774

If you want there to be at least 1 row with 9531 and at least 1 row with 9569 values for SCODE then:

SELECT stype
FROM   table_name
GROUP BY stype
HAVING COUNT(CASE scode WHEN 9531 THEN 1 END) > 0
AND    COUNT(CASE scode WHEN 9569 THEN 1 END) > 0

If you want exactly 1 row of each type then use:

SELECT stype
FROM   table_name
GROUP BY stype
HAVING COUNT(CASE scode WHEN 9531 THEN 1 END) = 1
AND    COUNT(CASE scode WHEN 9569 THEN 1 END) = 1

Upvotes: 1

Marko
Marko

Reputation: 988

select stype from table
group by stype
having count(distinct case when scode in (9531,9569) then scode else null end) = 2

Upvotes: 1

Related Questions