min - return more than 1 record

[solomon_13000]

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?

[cereal]

Hi,

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

[|-|x]

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.

[diafol]

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

ORDER BY unit_cm LIMIT 3