Here's a different take on it. The absence of aggregates makes it pretty easy to manipulate the output just about any way that you'd like and you'll never have to worry about a NULL aggregate message.
WITH
cteBase AS
(
SELECT BaseSupplierID, SupplierID, ProductID, Price
FROM dbo.Test1
WHERE BaseSupplierID = SupplierID
)
SELECT b.ProductID,
b.BaseSupplierID,
CurrentPrice = b.Price,
LowerPrice = t1.Price,
Savings = b.Price-t1.Price,
AlternateSupplierID = t1.SupplierID
FROM dbo.Test1 t1
JOIN cteBase b
ON t1.BaseSupplierID = b.BaseSupplierID
AND t1.ProductID = b.ProductID
AND t1.Price < b.Price
ORDER BY b.ProductID,
b.BaseSupplierID,
Savings DESC
;
Using the OP's readily consumable data, here's what the output looks like.
ProductID BaseSupplierID CurrentPrice LowerPrice Savings AlternateSupplierID
--------- -------------- ------------ ---------- ------- -------------------
1 3 11.00 10.00 1.00 5
3 5 16.00 10.00 6.00 9
3 5 16.00 14.00 2.00 8
(3 row(s) affected)
--Jeff Moden
Change is inevitable... Change for the better is not.