ok great!

the trick here is you really need two queries, adn compare them agaisnt each otehr;

you already have the cheapest supplier, but now you wnat to know which regular supplier might be cheaper than teh base supplier.

first, lets get the base supplier's prices:

/*

--results

Cheapest ProductID SupplierID BaseSupplierID

--------------------- ----------- ----------- --------------

11.00 1 3 3

10.00 2 4 4

16.00 3 5 5

*/

SELECT MIN(Price) as BasePrice, ProductID, SupplierID, BaseSupplierID

FROM test1

WHERE SupplierID= BaseSupplierID --where the supplier is the base supplier

GROUP By ProductID, SupplierID, BaseSupplierID

now using either a set of subqueries or CTE's, lets compare those two result sets: the results are identical, but CTE's are maybe a little easier to read.

--as subqueries:

SELECT * FROM(

SELECT MIN(Price) as Cheapest, ProductID, SupplierID, BaseSupplierID

FROM test1

GROUP By ProductID, SupplierID, BaseSupplierID

) TheCheapest

LEFT OUTER JOIN

(

SELECT MIN(Price) as BasePrice, ProductID, SupplierID, BaseSupplierID

FROM test1

WHERE SupplierID= BaseSupplierID

GROUP By ProductID, SupplierID, BaseSupplierID

) BaseSupplierPrices

ON TheCheapest.ProductID = BaseSupplierPrices.ProductID

WHERE TheCheapest.Cheapest < BaseSupplierPrices.BasePrice

As CTE's

With TheCheapest

AS

(

SELECT MIN(Price) as Cheapest, ProductID, SupplierID, BaseSupplierID

FROM test1

GROUP By ProductID, SupplierID, BaseSupplierID

) , BaseSupplierPrices

AS

(

SELECT MIN(Price) as BasePrice, ProductID, SupplierID, BaseSupplierID

FROM test1

WHERE SupplierID= BaseSupplierID

GROUP By ProductID, SupplierID, BaseSupplierID

)

SELECT *

FROM TheCheapest

LEFT OUTER JOIN BaseSupplierPrices

ON TheCheapest.ProductID = BaseSupplierPrices.ProductID

WHERE TheCheapest.Cheapest < BaseSupplierPrices.BasePrice