How can I list the products from a category where each supplier occoures at least once?-CodePudding

In a table there are products in different categories from different suppliers. How can I list the products from a category where each supplier occurs at least once?

For example:

select VendorName, count(*) as ItemsInElectronics
from product
where ProductCategorie = 'Electronics'
group by VendorName

VendorName	ItemsInElectronics
company1	14
company2	145
company3	25
company4	3
company5	543
company6	37
company7	88
company8	172
company9	249
company10	47

This is my current SQL statement to list the products in category Electronics

SELECT TOP(16) VendorName, ProductTitle, ProductCategory
FROM [dbo].[Product]
WHERE ProductCategory = 'Electronics'
ORDER BY CHECKSUM(NEWID())

VendorName	ProductTitle	ProductCategory
company1	Staubsauger	Electronics
company2	Lenovo ThinkStation	Electronics
company3	AMD Ryzen 7 5700G	Electronics
company2	Digittrade HS256 S3	Electronics
company4	Kaffeemaschine Delonghi	Electronics
company1	NanoCell Fernseher LG	Electronics
company4	H. Koenig Handstaubsauger	Electronics
company1	Wäschetrockner CANDY 8 kg	Electronics
company5	Kühl-/Gefrierkombination,	Electronics
company6	Asus ExpertBook B1500CEAE	Electronics
company7	HP M01-F1507nz (Intel Core i5	Electronics
company2	honor Notebook MagicBook 14	Electronics
company2	WD My Passport 1 TB Externe	Electronics
company5	Wasserkocher »Inox 2822CH1«	Electronics
company1	Smartphone überholt APPLE	Electronics
company2	Seagate One Touch Portable 5 TB Electronics

How can I set it up so that at least one product from each vendor is visible?

CodePudding user response：

You could do something like this, but it doesn't take into account what you want to do if there are more than 16 vendors:

;WITH src AS 
(
  SELECT VendorName, ProductTitle, ProductCategory,
    rn = ROW_NUMBER() OVER 
      (PARTITION BY VendorName ORDER BY CHECKSUM(NEWID()))
  FROM dbo.Product
  WHERE ProductCategory = 'Electronics'
)
-- make sure we get one row from each vendor:
SELECT VendorName, ProductTitle, ProductCategory
       FROM src WHERE rn = 1
UNION ALL
-- then if we want 16 rows total, take random rows from remainder:
SELECT TOP (16-(SELECT COUNT(DISTINCT VendorName) FROM src)) 
       VendorName, ProductTitle, ProductCategory 
       FROM src WHERE rn > 1;

Example db<>fiddle

If the TOP (16) has significance or you have requirements that differ if there are more than 16 vendors, you need to explain. (Here's one way you could deal with > 16 vendors and still get only one row per vendor.)

With the updated comment that you want more even distribution across vendors, you can do this slightly easier. This will return "chunks" of random products from each vendor, randomized within each chunk (so you might see products from company 1, 3, 5, 4, 2, then 2, 5, 4, 1, 3, and so on):

DECLARE @top tinyint = 24;

;WITH src AS 
(
  SELECT VendorName, ProductTitle, ProductCategory,
    rn = ROW_NUMBER() OVER 
      (PARTITION BY VendorName ORDER BY CHECKSUM(NEWID()))
  FROM dbo.Product
  WHERE ProductCategory = 'Electronics'
)
SELECT TOP (@top) VendorName, ProductTitle, ProductCategory
FROM src ORDER BY rn, NEWID();

While this one will return as even an overall distribution as possible, but more randomized (so you might see products from company 1, 2, 5, 2, 2, 3, 5, 1, 4, etc.):

DECLARE @top tinyint = 24;

;WITH src AS 
(
  SELECT VendorName, ProductTitle, ProductCategory,
    rn = ROW_NUMBER() OVER 
      (PARTITION BY VendorName ORDER BY CHECKSUM(NEWID()))
  FROM dbo.Product
  WHERE ProductCategory = 'Electronics'
)
SELECT * FROM (SELECT TOP (@top) VendorName, ProductTitle, ProductCategory
FROM src ORDER BY rn) x ORDER BY NEWID();

Updated db<>fiddle