SQL Query for grouping within a segment with a condition

We need to select all the columns.

Condition: Within a single MAT+PLANT If any vendor crosses 80% we take records of that vendor only and reject other records within that MAT+PLANT. If within a single MAT+PLANT If all the vendors have below 80%, we consider all the records.

As shown in the image: Have attached input and output

Answer

You can use exists:

select t.*
from t
where t.totalpercent >= 80.0 or
      not exists (select 1
                  from t t2
                  where t2.mat = t.mat and t2.totalpercent >= 80.0
                 );

You can also use window functions:

select t.*
from (select t.*,
             max(totalpercent) over (partition by mat) as max_totalpercent
      from t
     ) t
where percent >= 80.0 or max_totalpercent < 80.0;