min function

[Sarah76]

Hi there,

I have a table with a columns called unit_price and ingredient_ID and supplier _ID i need to find the cheapest unit price for each ingredient in that table!
can you help me to create a query which does it for me? so far i have this query which is not working:( I have to find the ingredients that are used for a 'pie' and also they have to be the cheapest as different suppliers offer different price

select min(Unit_Price) from  INGREDIENT_SUPPLIED 
having min(Unit_Price) - 1< ALL(select Unit_Price from  INGREDIENT_SUPPLIED 
where ingredient_ID = ANY(select Ingredient_ID from PRODUCT_INGREDIENT 
WHERE Product_Code =ALL (SELECT Product_Code FROM BAKERY_PRODUCT where Prod_Desc='Pie')));

[debasisdas]

you need ot use GROUP BY in the query.