How to fetch only single type using Oracle SQL-CodePudding

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

Result Set:-

Some one please help me on this.

CodePudding user response：

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.

CodePudding user response：

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
                 );

CodePudding user response：

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)
)