54

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?

Kate Orlova
  • 3,225
  • 5
  • 11
  • 35
Sami El Hilali
  • 981
  • 3
  • 12
  • 21

8 Answers8

85

this will give you result that has the minimum price on all records.

SELECT *
FROM pieces
WHERE price =  ( SELECT MIN(price) FROM pieces )
John Woo
  • 258,903
  • 69
  • 498
  • 492
72

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.

Troll
  • 1,895
  • 3
  • 15
  • 34
sberry
  • 128,281
  • 18
  • 138
  • 165
7

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)

fthiella
  • 48,073
  • 15
  • 90
  • 106
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;
pulsit
  • 169
  • 3
  • 5
5

Efficient way (with any number of records):

SELECT id, name, MIN(price) FROM (select * from table order by price) as t group by id
Smar
  • 8,109
  • 3
  • 36
  • 48
Vijay Maurya
  • 99
  • 1
  • 6
  • I have tried many solutions: using join, using subquery they all are good but consuming more time. but this one is fabulous. – Vijay Maurya Feb 06 '19 at 11:11
3

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

Daniel Rodriguez
  • 61
  • 5
0

To improve @sberry's answer, if the column has a null value then simply doing ORDER BY would select a row with null value. Add a WHERE clause to get correct results:

SELECT * FROM pieces 
WHERE price>0
ORDER BY price ASC 
LIMIT 1;

Or if there is a chance of having negative values and/or VARCHAR, etc. do:

SELECT * FROM pieces 
WHERE price IS NOT NULL
ORDER BY price ASC 
LIMIT 1;
theProcrastinator
  • 840
  • 9
  • 22
-9

To make it simpler

SELECT *,MIN(price) FROM prod LIMIT 1

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