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)