How to select data where a field has a min value in MySQL

Question

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

User · Answer

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

User · Answer

Use HAVING MIN       Something like   SELECT MIN price  AS price  pricegroup FROM articles prices WHERE articleID 10 GROUP BY pricegroup HAVING MIN price   gt  0

User · Answer

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

User · Answer

Efficient way  with any number of records    SELECT id  name  MIN price  FROM  select   from table order by price  as t group by id

User · Answer

To make it simpler     SELECT   MIN price  FROM prod LIMIT 1    Put   so it will display the all record of the minimum value

User · Answer

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

User · Answer

this will give you result that has the minimum price on all records   SELECT   FROM pieces WHERE price      SELECT MIN price  FROM pieces      SQLFiddle Demo

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

Examples related to mysql

Examples related to sql

Examples related to aggregate-functions

Examples related to greatest-n-per-group