How to select data where a field has a min value in MySQL?
this will give you result that has the minimum price on all records.
SELECT *FROM piecesWHERE price = ( SELECT MIN(price) FROM pieces )
This is how I would do it (assuming I understand the question)
SELECT * FROM pieces ORDER BY price ASC LIMIT 1If 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.
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)