Choose Lowest Price

[davedynamic]

I have a table which stores product codes and prices from various suppliers.

For example:
Code Price Supplier
101 $10.00 Someone
101 $12.50 Someone Else

What I want to create is a query to find the cheapest supplier for the product.

I can't seem to think of how to start?

 

[Pat Hartman]

You'll need two queries to do this.

query1:
Select Code, Min(Price) As MinPrice
From YourTable
Group by Code;

query2:
Select Q.Code, Q.MinPrice, T.Supplier
From query1 as Q Inner Join YourTable As T On Q.Code = T.Code and Q.MinPrice = T.Price;

You need to be aware that query2 may return multiple rows for a single produce if the price is the same for multiple suppliers.