The following SQL query would return 1 record with a min(unit_cm):

select id, name, category, min(unit_cm), unit_Cost, unit_price, unit_costPerc from (

SELECT a.id, b.name, e.name as 'category', (d.unit_price - d.unit_cost) as 'unit_cm', d.unit_cost, d.unit_price, ((d.unit_cost/d.unit_price)*100) as 'unit_costPerc'  
FROM menu_items a, item b, menu c, price d, categories e
where a.item_id_fk = b.id and a.menu_id_fk = c.id and b.id = d.item_id_fk and e.id = b.categories_id_fk ) tbl

I intend to get 3 records with a min(unit_cm). How can I achieve such a result?

Hi,

try to add ORDER BY unit_cm DESC LIMIT 3 to the subquery and remove min() from the main query.

ORDER BY unit_cm DESC LIMIT 3 will actually give you the 3 records with the highest values of unit_cm

To get the smallest values use ORDER BY unit_cm ASC LIMIT 3 in the subquery.

commented: Ops! Thanks for pointing my typo! ;D +13
Member Avatar

ASC is the default sorting direction, so you don't need to specify it.

ORDER BY unit_cm LIMIT 3
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.