Try this:
DECLARE @tmp AS TABLE (Id INT IDENTITY(1,1), ProductName VARCHAR(30), MainCategory VARCHAR(30), AdditionalCategory VARCHAR(30), size INT)
INSERT INTO @tmp (ProductName, MainCategory, AdditionalCategory, size)
VALUES('American Roast', 'Coffee', NULL, 10),
('American Roast', 'Coffee', NULL, 12),
('American Roast', 'Coffee', NULL, 14),
('American Roast', 'Coffee', NULL, 16),
('American Roast', 'Coffee', 'Hawaiian', 10),
('American Roast', 'Coffee', 'Hawaiian', 12),
('American Roast', 'Coffee', 'Hawaiian', 14),
('American Roast', 'Coffee', 'Hawaiian', 16),
('American Roast', 'Coffee', 'Kona', 10),
('American Roast', 'Coffee', 'Kona', 12),
('American Roast', 'Coffee', 'Kona', 14),
('American Roast', 'Coffee', 'Kona', 16)
SELECT t2.Id, t1.ProductName, t1.MainCategory, t1.AdditionalCategory, t1.size
FROM (
SELECT ProductName, MainCategory, AdditionalCategory, MIN(size) AS size
FROM @tmp
GROUP BY ProductName, MainCategory, AdditionalCategory
) AS t1 LEFT JOIN @tmp AS t2 ON t1.ProductName = t2.ProductName AND t1.MainCategory = t2.MainCategory
AND COALESCE(t1.AdditionalCategory, '-') = COALESCE(t2.AdditionalCategory,'-') AND t1.size = t2.size
Result:
1 American Roast Coffee NULL 10
5 American Roast Coffee Hawaiian 10
9 American Roast Coffee Kona 10
You can't add
Id
together with
MIN()
function, because it enforces to add
Id
to
GROUP BY
list. The only way to achieve expected output is to use subquery.