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?

Solution 1

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

Solution 2

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 minimum price, then @JohnWoo's answer should suffice.

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

Solution 3

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)

Solution 4

Efficient way (with any number of records):

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

Solution 5

Use HAVING MIN(...)

Something like:

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

View more solutions

Comments

[John Woo]

this question can have multiple interpretation, can you give example records with results?

[Laurentiu L.]

It actually wouldn't. * will display random values, not necessarily from the row associated with that min.

[Boris Schegolev]

Completely agree with Laurentiu L. here: most other DBMS (then MySQL) would not allow you to run such a query without proper GROUP BY clause.

[Leonardo Hermoso]

This will select a random row to min value and its not what was asked!

[Jeancarlo Fontalvo]

It looks like it can be reused in a view 😊

[Vijay Maurya]

I have tried many solutions: using join, using subquery they all are good but consuming more time. but this one is fabulous.

[Yamashiro Rion]

Please, tell me, will it work faster than a subquery?

[Haritsinh Gohil]

@YamashiroRion yes of course because it is simple and base query

[Stefano Martini]

in plus you saw "put * so it display the all record" but you add LIMIT 1 so you will get just one value.

[theProcrastinator]

But this would fail in case there is any NULL value in the column.