Oracle query for getting desired output

ColA ColB ColC ColD

data1 rata1 T Test1

data1 rata3 F Test2

data1 rata2 T Test1

data2 rata1 T Test1

data2 rata3 T Test1

data3 rata4 T Test1

开发者_JAVA技巧

Have four columns of type Varchar. What i wish to determine is for each unique value of ColB(rata1, rata2 etc), find corresponding value of ColA and other two columns (ColC, ColD) where that specific value of ColA occurs 0 or 1 time. For example- For rata1, data1 and data2 occurs one time so the output would include the columns shown below in addition to other columns for other distinct values of ColB.

data1    rata1    T     Test1
data2    rata1    T     Test1

Would highly appreciate if anyone can provide any recommendations around the same.

I think the below query will accomplish what you want.

With MyQuery as
(
     select ColA, ColB
       from MyTable
   group by ColA, ColB
     having count(*) <= 1
)
select ColA, ColB, ColC, ColD
  from MyTable t inner join MyQuery q on t.ColB = q.ColB and t.ColA = q.ColA

Edit to incorporate modification supplied by John C in the comments.

With MyQuery as
(
     select ColB
       from MyTable
   group by ColB
     having count(ColA) <= 1
)
select ColA, ColB, ColC, ColD
  from MyTable t inner join MyQuery q on t.ColA = q.ColA

what I understand from you question is that you want distinct values of ColB, and then want ColA,ColC and ColD corresponding to that ColB value. If that is the case you need not confuse yourself, just do order by ColB.

so query is

select * from TableA o left outer join
(select distinct ColA,ColB from TableA t)
 on o.ColA = t.ColA and o.ColB = t.ColB 
group by o.ColA having count(*) <= 1

With MyQuery as (select ColB from MyTable group by ColB having count(ColA) <= 1) select
t.ColB, ColA, ColC, ColD from MyTable t inner join MyQuery q on t.ColB = q.ColB