By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table product_attributes
(`id` int, `product_id` int, `product_name` varchar(3), `size` varchar(2), `stock` int)
;
insert into product_attributes
(`id`, `product_id`, `product_name`, `size`, `stock`)
values
(1, 1, 'XYZ', 'S', 2),
(2, 1, 'XYZ', 'M', 0),
(3, 1, 'XYZ', 'L', 3),
(7, 1, 'XYZ', 'XL', 3),
(4, 2, 'ABC', 'S', 1),
(5, 2, 'ABC', 'M', 1),
(6, 2, 'ABC', 'L', 1)
;
select product_id, product_name
from product_attributes
where `size` in ('S','M','L')
group by product_id,product_name
order by count(case when stock <> 0 then 1 end) desc
| product_id | product_name |
|---|---|
| 2 | ABC |
| 1 | XYZ |