CODEWITHSUNDEEP
sqloracle-database
user3726933
user3726933

Reputation: 349

Oracle query to find unique occurrences of a column in a table

Here's my table:

ID|2ndID|Value
1|ABC|103
2|ABC|102
3|DEF|103
4|XYZ|105

My query should return all instance of the ID that has only one Value=103 for the 2ndID. It shouldn't return Ids 1 and 2 because apart from 103, ABC has 102 too. 3|DEF on the other hand has only one Value = 103. And I need such rows back. I don't need the 4|XYZ also since value <> 103. Based on the above sample set my result should only be. 

3|DEF|103

I can use a group by 2ndID having COUNT(*) =1 which will return all but I don't know how to filter it only to Value = 103.

Thanks in advance.

Upvotes: 1

Views: 748

Answers (2)

user5683823
user5683823

Reputation:

This is a standard application of the HAVING clause in aggregate queries. You want to group by the second id, and select only the groups that have only one row and where the min(value) is 103. MIN(value) will be the unique value in the unique row, in the groups that only have one row to begin with; and you don't care about any other groups.

COMMENT: This solution assumes that the combination (second_id, value) is unique - it can't appear in the table more than once, for different id's. I asked the OP in a Comment under the original question to clarify whether this is in fact the case.

with
  mytable ( id, second_id, value ) as (
    select 1, 'ABC', 103 from dual union all
    select 2, 'ABC', 102 from dual union all
    select 3, 'DEF', 103 from dual union all
    select 4, 'XYZ', 105 from dual
  )
-- End of SIMULATED inputs (for testing only, NOT PART OF THE SOLUTION).
-- SQL query begins BELOW THIS LINE. Use your actual table and column names.
select   min(id) as id, second_id, min(value) as value
from     mytable
group by second_id
having   count(*) = 1 and min(value) = 103
;

ID  SECOND_ID  VALUE
--  ---------  -----
 3  DEF          103

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

This should return all the row with a single 2ndId value

select * from 
my_table where 2ndId in (

select 2ndId 
from my_table  
group by 2ndId
having count(*)  =1 
)

And if you need to enforce the filter for value 103 

select * from 
my_table where 2ndId in (

select 2ndId 
from my_table  
group by 2ndId
having count(*)  =1 
)
and value = 103

Upvotes: 1

Related Questions