[mysql] How to select data where a field has a min value in MySQL?

I want to select data from a table in MySQL where a specific field has the minimum value, I've tried this:

SELECT * FROM pieces WHERE MIN(price)

Please any help?

To make it simpler

SELECT *,MIN(price) FROM prod LIMIT 1

• Put * so it will display the all record of the minimum value

Use HAVING MIN(...)

Something like:

SELECT MIN(price) AS price, pricegroup
FROM articles_prices
WHERE articleID=10
GROUP BY pricegroup
HAVING MIN(price) > 0;

This also works:

SELECT
  pieces.*
FROM
  pieces inner join (select min(price) as minprice from pieces) mn
  on pieces.price = mn.minprice

(since this version doesn't have a where condition with a subquery, it could be used if you need to UPDATE the table, but if you just need to SELECT i would reccommend to use John Woo solution)

Efficient way (with any number of records):

SELECT id, name, MIN(price) FROM (select * from table order by price) as t group by id

In fact, depends what you want to get: - Just the min value:

SELECT MIN(price) FROM pieces

• A table (multiples rows) whith the min value: Is as John Woo said above.

• But, if can be different rows with same min value, the best is ORDER them from another column, because after or later you will need to do it (starting from John Woo answere):

  SELECT * FROM pieces WHERE price = ( SELECT MIN(price) FROM pieces) ORDER BY stock ASC

This is how I would do it (assuming I understand the question)

SELECT * FROM pieces ORDER BY price ASC LIMIT 1

If you are trying to select multiple rows where each of them may have the same price (which is the minimum) then @JohnWoo's answer should suffice.

Basically here we are just ordering the results by the price in ASCending order (increasing) and taking the first row of the result.