How to Select Rows with the Minimum Value in a MySQL Table?

Getting Minimal Fields in MySQL

Selecting data from a table where a specific field holds the minimum value can be a common task when managing data in MySQL. To do this, one must avoid the mistake of using the MIN() function incorrectly.

Incorrect Approach:

As mentioned in the query provided, using MIN(field) directly will not return the row with the lowest value. Instead, it will return the minimum value among all rows.

Correct Approach:

The solution lies in combining the MIN() function with the WHERE clause. Here's the correct query:

SELECT *
FROM pieces
WHERE price =  ( SELECT MIN(price) FROM pieces )

This query will retrieve all rows where the price field matches the minimum price obtained from the subquery.

Example:

Imagine a table named pieces with the following columns:

| id | name  | price |
|----|-------|-------|
| 1  | Bolt  | 3.50  |
| 2  | Nut   | 1.25  |
| 3  | Screw | 2.99  |
| 4  | Washer| 0.50  |

Executing the correct query will return the following result:

| id | name  | price |
|----|-------|-------|
| 4  | Washer| 0.50  |

Thus, the row with the lowest price (Washer with a price of 0.50) is extracted.

The above is the detailed content of How to Select Rows with the Minimum Value in a MySQL Table?. For more information, please follow other related articles on the PHP Chinese website!