Displaying records with minimum values only

[singlefin]

I'm trying to get my query to display only the minimum value within my query results. I have the following fields, CustomerID, OrderID, ProductID, WarehouseID, Freight Cost.

My current query will return results that show me the locations and freight cost of each product to my customers. My intention is for the query to only return records that has the lowest freight cost as there may be several warehouses with identical products but with varying freight costs due to location to customer.

I've tried to apply the MIN function on Freight Cost but it will only sort the records in ascending order instead of only displaying the record with lowest frieght cost.

Thanks in advance for your help.

 

[FoFa]

SELECT CustomerID, OrderID, ProductID, WarehouseID, (SELECT MIN(Freight Cost) from MyTable MT2 Where MT1.CustomerID = MT2.CustomerID) as MinFreight
from MYTABLE MT1