Lowest Cost

OK, this should be an easy one but my brain isn't quite working right now.

I have a table, we'll call Table1 like so:

ProductID Supplier Cost
12345 A 14.50
12345 B 13.49
12345 C 12.00
43222 A 15.00
43222 B 15.21
43222 C 13.99
12312 B 14.00
15421 A 21.99
15421 C 20.00

And I want to Get the name of the Supplier with the Lowest cost,
I know I can go like:

SELECT ProductID, MIN(Cost) FROM Table1 GROUP BY ProductID

and get the lowest cost, but what would be the most effiecent way to get all three fields returned by the query? I need the ProductID, Supplier and Cost.

Thanks,

select d.productid, t.supplier, d.cost
from table1 t
join
(
select productid, min(cost) cost
from table1
group by productid
) d on d.productid = t.productid and d.cost = t.cost

|||Thanks, that was what I was looking for. I'm still getting duplicate records if there are two suppliers with the same lowest cost for a product. But thats something I can work out.

Thanks.|||Just throw a DISTINCT on the outer query|||The extra distinct wont eliminate multiple suppliers with matching lowest prices

try adding a Top 1 to the outer query|||>>Just throw a DISTINCT on the outer query

>>>The extra distinct wont eliminate multiple suppliers with matching lowest prices
try adding a Top 1 to the outer query

You are correct mbanavige, I misread the problem to be the same supplier with multiple min(costs).

Though, TOP 1 will not do it either

DeoDev, I am having a hard time understanding why supplier has any relevance then in this resultset??|||Its for a ordering system, where it will automatically order products that are out of stock from the supplier who has the lowest price, so I need to know who has the lowest price so I can place the order with them, and I'd prefer to return the information in one table.

But with having two suppliers with the same lowest cost, It doesn't matter which one to order from out of the two. I think I have to do a little bit more thinking on the design of this function.|||I see.

Though I would hate to be the supplier whose name doesn't get returned in this result
:)