SQL removing duplicate based on actual/forecast-CodePudding

I have a table similar to below and was wondering if on days were there is an actual and forecast to remove the forecast row, and only keep the actuals?

enter image description here

CodePudding user response：

One way is to union with an existence check

select date,type,value from t where type ='actual'
union all
select date,type,value from t where type ='forecast' 
and not exists (select 1 from t t1 where t1.date = t.date and t1.type = 'actual') ;

And since there is no guarantee that and actual will have a forecast or a forecast will have an actual this is a bit simpler than simulating a full join, though not necessarily quicker.