6

sample table

id |   name   | price |
-----------------------
1  |   john   | 300   |
----------------------- 
2  | michael  |  400  |
----------------------- 
3  | michelle |  250  |
-----------------------

I will get the smallest number in the table using this query

SELECT id, name, MIN(price) FROM table

The result will become this:

_______________________
id |   name   | price |
-----------------------
1  | michelle |  250  |

I want the result will become like this:

id |   name   | price |
-----------------------
3  | michelle |  250  |
-----------------------

Thanks in advance!

Nejimz
  • 61
  • 1
  • 4
  • 5
    BTW, the lowest price in your sample dataset is 200, yet your desired output uses the price of 250. Is that an error, or do I not fully understand what you want? – Code Magician Nov 24 '11 at 01:15
  • 1
    I'd upvote this question, except your expected output doesn't match the question you're asking. (You're asking for the `MIN` price, but displaying output from something other than that, which makes no sense.) – Ken White Nov 24 '11 at 01:43
  • Sorry I wrote a wrong value id 2 should be 400. Thanks for you suggestions. – Nejimz Nov 24 '11 at 03:28

6 Answers6

8

The easiest way to get the id of the smallest number is this:

SELECT Id, name, price
FROM sampleTable
ORDER BY price ASC 
LIMIT 1;

If you want to use MIN (as the title states), one way to do it would be this:

SELECT id, name, price 
FROM sampleTable 
WHERE price = (SELECT MIN(price) from sampleTable)
Code Magician
  • 23,217
  • 7
  • 60
  • 77
  • Thanks M_M for this query. I prefer to use the 1st query because I need also to get the smallest price and ID. – Nejimz Nov 24 '11 at 03:30
4

Use a subquery:

SELECT id, name, price 
FROM table 
WHERE price = (SELECT min(price) FROM table);

Without a LIMIT 1, this can return multiple rows that all share the same minimum price. This may or may not be what you want instead of a arbitrary choice among the matching records.

Raymond Hettinger
  • 216,523
  • 63
  • 388
  • 485
3

For this query, you do not need MIN, but can use ORDER BY and LIMIT

SELECT id, name, price
FROM Table
ORDER BY Price ASC
LIMIT 1
Adam Wenger
  • 17,100
  • 6
  • 52
  • 63
3

I do it this way:

SELECT t1.id, t1.name, t1.price
FROM sampleTable AS t1
LEFT OUTER JOIN sampleTable AS t2
  ON t1.price > t2.price
WHERE t2.id IS NULL

In other words, look for a row t2 that has a lower price than the row t1. If no such row t2 is found, the outer join makes t2.* NULL, and when that's true, then t1 must have the minimum price.

This is called an exclusion join and it typically performs better than a subquery in MySQL.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
1

Do this;

SELECT id, name, price FROM table order by price ASC LIMIT 1

Hope it helps

Sudhir Bastakoti
  • 99,167
  • 15
  • 158
  • 162
0

Efficient way (with any number of records):-

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

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

But this one is fabulous.

Vijay Maurya
  • 99
  • 1
  • 6