CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle12c
SwapnaSubham Das
SwapnaSubham Das

Reputation: 537

How to fetch only single type using Oracle SQL

I want to fetch only those company for which there is only one type. I am using using Oracle 12C. Below is the sample dataset

enter image description here

Result Set:-

enter image description here

Some one please help me on this.

Upvotes: 0

Views: 46

Answers (3)

Ed Gibbs
Ed Gibbs

Reputation: 26343

Another way is to compare MIN(TYPE) for each company with its MAX(TYPE). If they're the same then it only has one TYPE.

SELECT CompanyId, Type
FROM myTable
WHERE COMPANYID IN (
  SELECT COMPANYID
  FROM myTable
  GROUP BY COMPANYID
  HAVING MIN(TYPE) = MAX(TYPE)
)

Upvotes: 0

user5683823
user5683823

Reputation: 

select companyid, min(type_) as type_
from   [table_name]
group  by companyid
having min(type_) = max(type_)
;

Replace [table_name] with your actual type name. Note also that I used type_ (with an underscore) for the column name. I hope your column name is not type, which is a reserved keyword; if it is, change it.

A possible alternative is to use having count(distinct(type_)) = 1 - but that is a poor solution. It requires a distinct operation within each group (by companyid). By contrast, min and max are much easier to keep track of.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can use not exists:

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

Upvotes: 1

Related Questions