[RESOLVED] Trouble with MIN Value

[taod]

HI Folks, I have a problem in using the MIN function for MYSQL

I want to get the supplier of the lowest cost of a product inside a table based on product name

I currently have this data set...

Name | Cost | SupplierName
ProductA | 24.70 | SupplierA
ProductA | 22.00 | SupplierB

And my initial work produced the following query,

SELECT SupplierName, MIN(cost) as mincost FROM tableName GROUP BY Name

What it gives me is the minCost value but it does not give me the right supplier.

😕

How can i get it right? I did try the HAVING clause but that just limited my result set

[Piranha]

I can think of two different solutions:

1. If you only want to get one product you can use the following (with having):

SELECT suppliername, cost
FROM tableName
HAVING cost = (
SELECT min( cost )
FROM tableName );

2. Another way to do more or less the same thing is to order the result and limit the number of answers:

SELECT suppliername, cost
FROM test2
ORDER BY cost
LIMIT 0 , 1

3. If you want to get get different products you could use this:

SELECT suppliername, cost
FROM test2
where (cost, name) in (
SELECT min( cost ), name
FROM test2
group by name);

[taod]

Cool thanks,

I havent done sub queries for a while since I was forced to use mysql 4 for some of our older webservers.