Hi, this should get you somewhere near:
;with cte as (
select ca.attributeId
, ca.value as ca_value
, sa.supplierId
, sa.value as sa_value
from ClientAttribute ca
join SupplierAttribute sa
on ca.attributeId = sa.attributeId
where ca.clientId = 1
)
, cte2 as (
select s.supplierId
, sum(case when ca_value = 2 and sa_value = 1 then 1 else 0 end) as req_count
, sum(case when ca_value = 1 and sa_value = 1 then 1 else 0 end) as pref_count
, s.rate
from cte
join Supplier s
on cte.supplierId = s.supplierId
where s.supplierId not in (
select supplierId
from cte
where ca_value = 2
and sa_value = 0
)
group by s.supplierId, rate
)
select * from cte2
order by req_count desc, pref_count desc, rate desc
Regards, Iain