So if a search for category 1 in this table would give 1 & 2 as suppliers.
But I would like the order to be that supplier 2 has priority because it only has 1 product and supplier 1 would come second because it has 2 products.
So the more products a supplier has they come last in the order result.
Yeah there are aggregate functions like COUNT which will give you a count of results. Mix that with the GROUP BY clause and you can get what you like.
To take your example, if you group the rows on supplier ID you would end up with a count of 2 for supplier 1 and 1 for supplier 2 when you search based on category. Once you have a count, you can then order the count in ascending order and take the top row (or one of the rows that have the same product count if you have more than one).
I believe something like this will get you there…
SELECT sid, count(pid) as product_count FROM yourtablehere WHERE cid = 1 GROUP BY sid ORDER BY product_count asc
Here we are saying return records with the supplier id and a count of products and call that column product_count. We are going to group by the supplier id (this groups the product counts in relation to the supplier). Now on we want records matching category ID 1 and lastly order those by the product_count in ascending order. In cases where you have multiple suppliers that have the same product count for a given category, you will need some other criteria on knowing which to choose otherwise if you always take the first row the same supplier will get priority